Что такое первичный ключ и внешний ключ? Какова их роль в базе данных?

Первичный и Внешний ключи в базах данных

Первичные и внешние ключи — это основные механизмы, которые обеспечивают целостность данных и связь между таблицами в реляционных базах данных. Разберу их назначение, различия и применение на практике.

Первичный ключ (Primary Key)

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

Свойства первичного ключа:

• Уникальность: нет двух строк с одинаковым значением
• Обязательность (NOT NULL): не может быть пустым значением
• Один первичный ключ на таблицу: можешь выбрать только одно поле (простой) или комбинацию полей (составной)
• Индексирование: автоматически создаётся индекс для быстрого поиска

Типы первичных ключей:

1. Простой первичный ключ (одно поле):

CREATE TABLE users (
    user_id INT PRIMARY KEY,  -- Первичный ключ
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE
);

1. Составной первичный ключ (комбинация полей):

CREATE TABLE order_items (
    order_id INT,
    product_id INT,
    quantity INT,
    PRIMARY KEY (order_id, product_id)  -- Комбинация двух полей
);

Здесь пара (order_id, product_id) должна быть уникальна. Один заказ может содержать один продукт только один раз.

1. Суррогатный ключ (автоматически генерируемый):

CREATE TABLE products (
    product_id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    price DECIMAL(10, 2)
);

Значение product_id создаётся автоматически (1, 2, 3...).

1. Естественный ключ (основан на реальных данных):

CREATE TABLE countries (
    country_code CHAR(2) PRIMARY KEY,  -- ISO код (US, RU, GB)
    country_name VARCHAR(100) NOT NULL
);

Внешний ключ (Foreign Key)

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

Свойства внешнего ключа:

• Ссылка на другую таблицу: указывает на первичный ключ в другой таблице
• Ограничение целостности: база данных проверяет, что значение существует в связанной таблице
• Может быть NULL: в отличие от первичного ключа
• Может быть не уникальным: несколько строк могут иметь одно и то же значение

Синтаксис внешнего ключа:

CREATE TABLE orders (
    order_id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    order_date TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(user_id)  -- Ссылаемся на первичный ключ users
);

Здесь order_id — первичный ключ таблицы orders, а user_id — внешний ключ, который ссылается на user_id из таблицы users.

Практический пример: интернет-магазин

-- Таблица пользователей
CREATE TABLE users (
    user_id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100) NOT NULL UNIQUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Таблица заказов
CREATE TABLE orders (
    order_id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    order_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    total_amount DECIMAL(10, 2),
    FOREIGN KEY (user_id) REFERENCES users(user_id)  -- Ссылаемся на users
);

-- Таблица продуктов
CREATE TABLE products (
    product_id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    price DECIMAL(10, 2) NOT NULL,
    category_id INT,
    FOREIGN KEY (category_id) REFERENCES categories(category_id)
);

-- Таблица деталей заказа
CREATE TABLE order_items (
    order_item_id INT PRIMARY KEY AUTO_INCREMENT,
    order_id INT NOT NULL,
    product_id INT NOT NULL,
    quantity INT NOT NULL,
    price DECIMAL(10, 2),
    FOREIGN KEY (order_id) REFERENCES orders(order_id),
    FOREIGN KEY (product_id) REFERENCES products(product_id)
);

Роль в базе данных

1. Обеспечение целостности данных

Внешние ключи предотвращают некорректные операции:

-- Попытка вставить заказ несуществующего пользователя
INSERT INTO orders (user_id, order_date, total_amount)
VALUES (999, NOW(), 1000);
-- ERROR: Foreign key constraint fails!
-- Пользователя с user_id = 999 не существует

2. Перекрёстные ссылки

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

-- Найти все заказы пользователя
SELECT o.order_id, o.order_date, o.total_amount
FROM orders o
WHERE o.user_id = 5;  -- user_id — это внешний ключ

-- Найти детали заказа с информацией о продукте
SELECT oi.quantity, p.name, p.price, (oi.quantity * p.price) as line_total
FROM order_items oi
JOIN products p ON oi.product_id = p.product_id
WHERE oi.order_id = 10;

3. Каскадные операции

Можно настроить автоматическое обновление/удаление связанных записей:

CREATE TABLE orders (
    order_id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    order_date TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(user_id)
        ON DELETE CASCADE        -- При удалении пользователя, удаляются все его заказы
        ON UPDATE CASCADE        -- При изменении user_id, обновляются все заказы
);

Опции для каскадных операций:

• CASCADE: автоматически удалить/обновить связанные строки
• RESTRICT: запретить удаление, если есть связанные строки
• SET NULL: установить значение внешнего ключа в NULL
• SET DEFAULT: установить значение по умолчанию

Разница в одной таблице

Лучшие практики

1. Всегда используй первичный ключ: каждая таблица должна иметь первичный ключ
2. Предпочитай суррогатные ключи: они проще и надёжнее естественных
3. Документируй связи: явно указывай, какие внешние ключи есть в таблице
4. Индексируй внешние ключи: это улучшит производительность JOIN-ов
5. Выбирай правильную стратегию каскадирования: обычно CASCADE используют редко

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