Зв’язки даних

Перш ніж зануритися у взаємозв’язки, необхідно пояснити основні поняття про те, як дані посилаються між різними таблицями.

Первинний ключ (Primary key)

Первинний ключ вказує на те, що стовпець або група стовпців може використовуватися як унікальний ідентифікатор для рядків у таблиці. Це вимагає, щоб значення були і унікальними, і не нульовими. Отже, наступні два визначення таблиці приймають однакові дані:

CREATE TABLE publisher
(
    id      INTEGER PRIMARY KEY,
    ...
);

CREATE TABLE publisher
(
    id      INTEGER NOT NULL UNIQUE,
    ...
);

Первинні ключі можуть охоплювати більше одного стовпця; синтаксис подібний до унікального обмеження:

CREATE TABLE example
(
    a INTEGER,
    b INTEGER,
    c INTEGER,
    PRIMARY KEY (a, c)
);

Додавання первинного ключа автоматично створить унікальний індекс B-дерева для стовпця або групи стовпців, перелічених у первинному ключі, і примусить стовпець(и) позначити NOT NULL.

Таблиця може мати не більше одного первинного ключа.

Примітка

Обмежень може бути будь-яка кількість унікальних і не нульових, які функціонально майже однакові, але тільки одне з них може бути ідентифіковане як первинний ключ.

Зовнішній ключ (FK)

Зовнішній ключ визначає, що значення у стовпчику (або групі стовпчиків) має збігатися зі значеннями, що з’являються у деякому рядку іншої таблиці. Ми говоримо, що це підтримує посилальну цілісність між двома таблицями.

Таблиця може мати більше одного обмеження зовнішнього ключа. Зовнішній ключ також може перекриватися з первинним ключем.

Щоб визначити FK:

CREATE TABLE book
(
    id           SERIAL PRIMARY KEY,
    publisher_id INTEGER REFERENCES publisher
);

CREATE TABLE book
(
    id           SERIAL PRIMARY KEY,
    publisher_id INTEGER REFERENCES publisher (id)
);

CREATE TABLE book
(
    id           SERIAL PRIMARY KEY,
    publisher_id INTEGER,
    FOREIGN KEY (publisher_id) REFERENCES publisher
);

У цій ситуації таблиця book є таблицею посилання, а таблиця publisher є таблицею посилання. Аналогічно існують стовпці, на які є посилання, і стовпці, на які є посилання.

Якщо стовпець, на який є посилання, не визначено явно, буде використано первинний ключ таблиці, на яку посилаються.

Неможливо створити рядок з ненульовим зовнішнім ключем, який не з’являється в таблиці, на яку посилається.

Самореферентний зовнішній ключ

Іноді корисно, щоб таблиця, на яку посилаються, збігалася з таблицею, на яку посилаються. Це дозволяє створювати деревоподібні структури з вузлами, де вузол верхнього рівня матиме NULL parent_id.

CREATE TABLE tree
(
    id        SERIAL PRIMARY KEY,
    parent_id INTEGER REFERENCES tree
);

Видалення рядків з посиланнями

Досі ми знали, що зовнішні ключі не дозволяють створювати рядки, які не пов’язані з жодним рядком всередині таблиці, на яку посилаються. Але що робити, якщо рядок, на який є посилання, потрібно видалити? Інтуїтивно зрозуміло, що варіантів небагато:

заборонити видалення
також видалити рядки посилань
щось інше…

Обмеження та каскадне видалення є двома найпоширенішими варіантами. ОБМЕЖЕННЯ запобігає видаленню рядка, на який є посилання. CASCADE також видаляє всі рядки, на які є посилання, а також рядок, на який є посилання.

CREATE TABLE book
(
    id           SERIAL PRIMARY KEY,
    publisher_id INTEGER REFERENCES publisher ON DELETE RESTRICT
);

CREATE TABLE book
(
    id           SERIAL PRIMARY KEY,
    publisher_id INTEGER REFERENCES publisher ON DELETE CASCADE
);

Є й інші варіанти.

NO ACTION означає, що якщо будь-які рядки посилань все ще існують, коли перевіряється обмеження, буде видано помилку; це поведінка за замовчуванням.

SET NULL замінить значення зовнішнього ключа у всіх рядках, що посилаються, на значення NULL.

SET DEFAULT замінить значення зовнішніх ключів усіх посилальних рядків значеннями за замовчуванням.

Відносини

З точки зору СУБД, між двома таблицями не існує інших зв’язків, окрім зовнішнього ключа. Але те, як спроектовані зовнішні ключі, надає три концепції зв’язків між даними.

article та comment мають відношення один-до-багатьох. Стаття може бути контейнером для багатьох коментарів, але коментар пов’язаний лише з однією статтею.

У реляційній базі даних дві таблиці мають зв’язок «один до багатьох», якщо кожен рядок однієї таблиці посилається на декілька рядків іншої таблиці. Наприклад, видавець може видавати багато книжок, але кожна окрема книжка видається певним видавництвом.

CREATE TABLE book
(
    id               SERIAL PRIMARY KEY,
    title            VARCHAR(255) NOT NULL,
    synopsis         TEXT,
    isbn             VARCHAR(16),
    publisher_id     INTEGER      NOT NULL REFERENCES publisher ON DELETE RESTRICT,
    publication_date DATE,
    language         VARCHAR(64),
    page_count       INTEGER,
    keywords         TEXT
);

COMMENT
    ON TABLE book IS 'available books';
COMMENT
    ON COLUMN book.title IS 'non-zero length book''s title';
COMMENT
    ON COLUMN book.keywords IS 'whitespace separated keywords';

ALTER TABLE book
    ADD CONSTRAINT check_title_length CHECK (LENGTH(title) > 0);

DROP TYPE IF EXISTS book_genre;
CREATE TYPE book_genre AS ENUM (
    'Adventure',
    'Biography',
    'Comedy',
    'Crime',
    'Drama',
    'Fantasy',
    'Historical Fiction',
    'Horror',
    'Mystery',
    'Poetry',
    'Romance',
    'Science Fiction',
    'Self-Help',
    'Thriller',
    'Young Adult'
    );

COMMENT
    ON TYPE book_genre IS 'registered book genres enumeration';

ALTER TABLE book
    ADD COLUMN genre book_genre;

ALTER TABLE book
    OWNER TO libms;

Один-до-одного

Примітка

Ви можете думати про відносини один-на-один як про обмежену версію «один-до-багатьох».

У реляційній базі даних дві таблиці мають зв’язок «один до одного», якщо кожен рядок в одній таблиці посилається на точно такий самий рядок в іншій таблиці, і навпаки. Наприклад, таблиця members і таблиця contacts можуть мати зв’язок «один до одного».

CREATE TABLE member
(
    id         SERIAL PRIMARY KEY,
    first_name VARCHAR(64) NOT NULL CHECK (LENGTH(first_name) > 0),
    last_name  VARCHAR(64) NOT NULL CHECK (LENGTH(last_name) > 0),
    dob        DATE,
    registered DATE DEFAULT NOW(),
    contact_id INTEGER UNIQUE REFERENCES contact
);

COMMENT
    ON TABLE member IS 'library registered members';
COMMENT
    ON COLUMN member.contact_id IS '1-to-1 relationship to contacts table';

ALTER TABLE member
    OWNER TO libms;

-- this is the default member to reference, in case actual member is deleted
INSERT INTO member
VALUES (0, 'Deleted', 'User', NULL, NULL, NULL);

Багато до багатьох

У реляційній базі даних дві таблиці мають зв’язок «багато до багатьох», якщо кожен рядок в одній таблиці може посилатися на декілька рядків в іншій таблиці, і навпаки. Наприклад, таблиця book може містити записи про книги, написані багатьма authors. З іншого боку, автор може написати декілька книг. Реалізація зв’язку «багато-до-багатьох» вимагає створення проміжної таблиці, яка застосовує зв’язок «один-до-багатьох» до обох вихідних таблиць.

CREATE TABLE book_author
(
    book_id   INTEGER REFERENCES book,
    author_id INTEGER REFERENCES author
);

COMMENT
    ON TABLE book_author IS 'books-to-authors relationship';
COMMENT
    ON COLUMN book_author.book_id IS 'unique together with author_id';
COMMENT
    ON COLUMN book_author.author_id IS 'unique together with book_id';

ALTER TABLE book_author
    OWNER TO libms;