PostgreSQL / Гадаад түлхүүр (FOREIGN KEY)

Гадаад түлхүүр (FOREIGN KEY)

Бодит системд мэдээлэл нэг хүснэгтэд бүгд багтдаггүй — хэрэглэгч, захиалга, бараа гэх мэт олон хүснэгт хоорондоо холбоотой байдаг. FOREIGN KEY бол яг энэ холбоосыг баталгаажуулдаг механизм юм. "Байхгүй хэрэглэгчийн захиалга" эсвэл "байхгүй барааны жагсаалт" гэх мэт утгагүй мэдээлэл орохоос сэргийлдэг.

FOREIGN KEY гэж юу вэ?

Foreign key нь нэг хүснэгтийн баганыг өөр хүснэгтийн PRIMARY KEY-тэй холбодог хязгаарлалт юм. Жишээ нь:

sql

-- Эх хүснэгт (parent table)
CREATE TABLE authors (
    id   BIGSERIAL PRIMARY KEY,
    нэр  TEXT NOT NULL
);

-- Хүүхэд хүснэгт (child table)
CREATE TABLE posts (
    id        BIGSERIAL PRIMARY KEY,
    гарчиг    TEXT NOT NULL,
    author_id BIGINT REFERENCES authors(id)  -- foreign key
);

REFERENCES authors(id) гэдэг нь author_id-д оруулах утга authors хүснэгтийн id-д заавал байх ёстой гэсэн утга. Байхгүй author_id оруулахыг оролдвол алдаа гарна:

sql

INSERT INTO posts (гарчиг, author_id) VALUES ('Миний нийтлэл', 999);
-- ERROR:  insert or update on table "posts" violates foreign key constraint
-- DETAIL:  Key (author_id)=(999) is not present in table "authors".

REFERENCES бичих хэлбэрүүд

Inline хэлбэр (нэг баганад):

sql

CREATE TABLE posts (
    id        BIGSERIAL PRIMARY KEY,
    author_id BIGINT NOT NULL REFERENCES authors(id)
);

CONSTRAINT нэртэй хэлбэр (илүү тодорхой):

sql

CREATE TABLE posts (
    id        BIGSERIAL PRIMARY KEY,
    author_id BIGINT NOT NULL,
    CONSTRAINT posts_author_id_fkey
        FOREIGN KEY (author_id) REFERENCES authors(id)
);

Нийлмэл foreign key (хоёр баганаар холбох):

sql

CREATE TABLE order_items (
    order_id   INTEGER NOT NULL,
    product_id INTEGER NOT NULL,
    тоо        INTEGER NOT NULL,
    PRIMARY KEY (order_id, product_id),
    FOREIGN KEY (order_id)   REFERENCES orders(id),
    FOREIGN KEY (product_id) REFERENCES products(id)
);

ON DELETE — устгахад юу хийх вэ?

Эх бичлэг устгагдахад хүүхэд бичлэгт юу болохыг тодорхойлно:

| Сонголт | Тайлбар | |---------|---------| | RESTRICT | Хүүхэд бичлэг байвал устгахыг хориглоно (өгөгдмөл) | | CASCADE | Хүүхэд бичлэгүүдийг хамт устгана | | SET NULL | Хүүхэд баганыг NULL болгоно | | SET DEFAULT | Хүүхэд баганыг DEFAULT утга болгоно | | NO ACTION | RESTRICT-тэй ижил, шалгалтыг хойшлуулж болно |

sql

-- Зохиогч устгагдахад нийтлэлүүд нь хамт устна
CREATE TABLE posts (
    id        BIGSERIAL PRIMARY KEY,
    гарчиг    TEXT NOT NULL,
    author_id BIGINT REFERENCES authors(id) ON DELETE CASCADE
);

-- Хэрэглэгч устгагдахад захиалгын user_id нь NULL болно
CREATE TABLE orders (
    id      BIGSERIAL PRIMARY KEY,
    user_id BIGINT REFERENCES users(id) ON DELETE SET NULL
);

-- Эх категори устгахыг хориглоно (доор нь бараа байвал)
CREATE TABLE products (
    id          BIGSERIAL PRIMARY KEY,
    category_id INTEGER REFERENCES categories(id) ON DELETE RESTRICT
);

ON UPDATE — шинэчлэхэд юу хийх вэ?

Эх бичлэгийн PRIMARY KEY өөрчлөгдөхөд ховор ашиглагдана. CASCADE хамгийн нийтлэг:

sql

CREATE TABLE posts (
    id        BIGSERIAL PRIMARY KEY,
    author_id BIGINT REFERENCES authors(id)
        ON DELETE CASCADE
        ON UPDATE CASCADE
);

Бодит жишээ: e-commerce схем

sql

-- Хэрэглэгчид
CREATE TABLE users (
    id       BIGSERIAL PRIMARY KEY,
    нэр      TEXT NOT NULL,
    имэйл    TEXT UNIQUE NOT NULL
);

-- Барааны ангилал
CREATE TABLE categories (
    id   BIGSERIAL PRIMARY KEY,
    нэр  TEXT UNIQUE NOT NULL
);

-- Бараа
CREATE TABLE products (
    id          BIGSERIAL PRIMARY KEY,
    нэр         TEXT NOT NULL,
    үнэ         NUMERIC(10,2) NOT NULL CHECK (үнэ > 0),
    category_id BIGINT NOT NULL REFERENCES categories(id) ON DELETE RESTRICT
);

-- Захиалга
CREATE TABLE orders (
    id       BIGSERIAL PRIMARY KEY,
    user_id  BIGINT NOT NULL REFERENCES users(id) ON DELETE RESTRICT,
    нийт     NUMERIC(12,2) NOT NULL,
    үүсгэсэн TIMESTAMPTZ DEFAULT NOW()
);

-- Захиалгын мөрүүд
CREATE TABLE order_items (
    id         BIGSERIAL PRIMARY KEY,
    order_id   BIGINT NOT NULL REFERENCES orders(id)   ON DELETE CASCADE,
    product_id BIGINT NOT NULL REFERENCES products(id) ON DELETE RESTRICT,
    тоо        INTEGER NOT NULL CHECK (тоо > 0),
    үнэ        NUMERIC(10,2) NOT NULL
);

Foreign key шалгах

Хүснэгтийн бүх foreign key-г харах:

sql

SELECT
    tc.constraint_name,
    tc.table_name,
    kcu.column_name,
    ccu.table_name  AS referenced_table,
    ccu.column_name AS referenced_column
FROM information_schema.table_constraints AS tc
JOIN information_schema.key_column_usage  AS kcu
    ON tc.constraint_name = kcu.constraint_name
JOIN information_schema.constraint_column_usage AS ccu
    ON ccu.constraint_name = tc.constraint_name
WHERE tc.constraint_type = 'FOREIGN KEY'
  AND tc.table_name = 'posts';

Дараагийн хичээлд:

Харилцаат хүснэгтүүдийг INNER JOIN-аар нэгтгэж, нэг query-д олон хүснэгтийн мэдээллийг хамтад нь уншиж сурна — SQL-ийн хамгийн хүчирхэг хэрэгслийг нэгдүгээрт судална.

← Өмнөх хичээлХязгаарлалт (PRIMARY KEY, UNIQUE, NOT NULL)Дараах хичээл →INNER JOIN