Внешние ключи: FOREIGN KEY

Представьте, что у вас есть две таблицы: одна с данными о студентах и другая с данными о курсах. Как вы думаете, как можно выразить информацию о том, какие студенты записаны на какие курсы? Можно добавить столбец в таблицу студентов для хранения всех курсов, на которые они записаны, но это быстро приведет к хаосу. Одно из лучших решений — это использовать внешние ключи!

Внешний ключ (FOREIGN KEY) — это столбец или набор столбцов, который используется для создания связи между таблицами. Он указывает на столбец (обычно PRIMARY KEY) в другой таблице, обеспечивая ссылочную целостность данных между ними. Так что, если какой-то студент записан на курс, мы можем быть уверены, что этот курс действительно существует.

Внешние ключи:

• Помогают поддерживать целостность данных, гарантируя, что записи в одной таблице соответствуют данным в другой.
• Делают базу данных понятной и логически организованной.
• Упрощают обработку данных, предотвращают дублирование и ошибки.

Пример из реальной жизни

Представьте библиотеку. Таблица books содержит список книг, а таблица members — список читателей. Чтобы отслеживать, кто взял какую книгу, мы можем создать таблицу borrowed_books, которая будет ссылаться на books и members через внешние ключи. Таким образом, каждый раз, когда кто-то берет книгу, мы точно знаем, кто это был, и какую именно книгу они взяли.

Типы связей между таблицами

Внешние ключи позволяют описывать различные связи между таблицами. Давайте разберем три основных типа:

1. Связь "Один-к-Одному" (ONE-TO-ONE)

Это самая простая связь, где одна запись в одной таблице соответствует ровно одной записи в другой таблице. Например, таблица users может содержать информацию о пользователях, а таблица profiles может содержать дополнительную информацию, такую как их адреса или фотографии.

Пример SQL для связи ONE-TO-ONE:

CREATE TABLE users (
    user_id SERIAL PRIMARY KEY,
    username TEXT NOT NULL
);

CREATE TABLE profiles (
    profile_id SERIAL PRIMARY KEY,
    user_id INT UNIQUE REFERENCES users(user_id), -- ссылается на users.user_id
    address TEXT
);

Здесь столбец user_idв таблице profiles выступает как внешний ключ, ссылающийся на user_idв таблице users.

Пример таблицы users:

Пример таблицы profiles:

2. Связь "Один-ко-Многим" (ONE-TO-MANY)

Это самая популярная связь. Например, один клиент может сделать множество заказов. В этом случае таблица customers будет связана с таблицей orders с помощью внешнего ключа.

Пример SQL для связи ONE-TO-MANY:

CREATE TABLE customers (
    customer_id SERIAL PRIMARY KEY,
    name TEXT NOT NULL
);

CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    customer_id INT REFERENCES customers(customer_id),	-- ссылается на customers.customer_id
    order_date DATE NOT NULL
);

Таблица orders может содержать множество записей, указывающих на одну запись в таблице customers

Пример таблицы customers:

Пример таблицы orders:

3. Связь "Многие-ко-Многим" (MANY-TO-MANY)

Иногда одна запись в таблице может быть связана с несколькими записями в другой таблице, и наоборот. Например, студенты записываются на курсы, а на одном курсе может быть множество студентов. Для этого обычно создается промежуточная таблица.

Пример SQL для связи MANY-TO-MANY:

-- студенты
CREATE TABLE students (
    student_id SERIAL PRIMARY KEY,
    name TEXT NOT NULL
);

-- курсы
CREATE TABLE courses (
    course_id SERIAL PRIMARY KEY,
    title TEXT NOT NULL
);

-- "запись определенного студента на определенный курс"
CREATE TABLE enrollments (
    student_id INT REFERENCES students(student_id), -- ссылка на id-студента
    course_id INT REFERENCES courses(course_id),	-- ссылка на id-курса
    PRIMARY KEY (student_id, course_id)
);

Здесь таблица enrollments играет роль "моста" между таблицами students и courses.

Пример таблицы students:

Пример таблицы courses:

Пример таблицы enrollments:

Преимущества использования внешних ключей

1. Данные остаются последовательными. Если внешний ключ указывает на несуществующую запись, PostgreSQL не позволит вставить или обновить данные.
2. Упрощение работы с таблицами. Внешние ключи позволяют легко связывать данные между таблицами и выполнять сложные запросы, такие как JOIN.
3. Автоматическое поведение. Вы можете настроить действия при удалении или обновлении связанных записей (например, каскадное удаление, установка NULL и т.д.).

Как выглядит внешний ключ в PostgreSQL?

Синтаксис для создания внешнего ключа при создании таблицы:

CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    customer_id INT REFERENCES customers(customer_id)   -- внешний ключ-ссылка
);

Здесь:

• customer_id — это внешний ключ.
• Он ссылается на customer_id в таблице customers.

Вы также можете явно указать внешний ключ с помощью FOREIGN KEY:

CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    customer_id INT,
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

Интересный факт

Сам термин "внешний ключ" (FOREIGN KEY) пришёл из теории реляционных баз данных, разработанной в 1970-х годах Эдгаром Коддом. Удивительно, но основная идея настолько мощна, что до сих пор является ключевым элементом современных баз данных. Кто сказал, что классика не актуальна?

Теперь вы понимаете, что такое внешний ключ и почему он так важен. Вам еще предстоит узнать, как создать их при создании таблиц и как правильно использовать каскадные операции. Но это уже в следующих лекциях!