Какие типы ключей используются в SQL?

Типы ключей в SQL: полный обзор и применение

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

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

Определение: уникальный идентификатор каждой записи в таблице. Не может быть NULL.

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

-- Или альтернативный синтаксис
CREATE TABLE users (
    user_id BIGINT,
    email VARCHAR(255) NOT NULL,
    username VARCHAR(100) NOT NULL,
    PRIMARY KEY (user_id)
);

-- Составной первичный ключ (composite primary key)
CREATE TABLE user_preferences (
    user_id BIGINT,
    preference_key VARCHAR(100),
    preference_value TEXT,
    PRIMARY KEY (user_id, preference_key)  -- Комбинация уникальна
);

Характеристики:

• Автоматически создаётся индекс
• Уникален и не NULL
• По одному на таблицу
• Оптимизирует JOIN операции

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

Определение: указывает на первичный ключ другой таблицы. Обеспечивает referential integrity (целостность ссылок).

CREATE TABLE orders (
    order_id BIGINT PRIMARY KEY,
    user_id BIGINT NOT NULL,
    order_date TIMESTAMP,
    total DECIMAL(10, 2),
    -- Внешний ключ, указывает на users.user_id
    FOREIGN KEY (user_id) REFERENCES users(user_id)
);

-- С каскадным удалением
CREATE TABLE order_items (
    item_id BIGINT PRIMARY KEY,
    order_id BIGINT NOT NULL,
    product_id BIGINT NOT NULL,
    quantity INT,
    FOREIGN KEY (order_id) REFERENCES orders(order_id)
        ON DELETE CASCADE  -- Удалить items если удалён order
        ON UPDATE CASCADE  -- Обновить items если обновлён order
);

-- Проверка целостности
INSERT INTO orders (order_id, user_id)
VALUES (1, 99999);  -- ERROR! user_id 99999 не существует в users

Опции действия при изменении:

• CASCADE: удалить/обновить зависимые записи
• RESTRICT: запретить удаление если есть зависимые записи
• SET NULL: установить NULL в зависимых записях
• NO ACTION: как RESTRICT (проверка в конце транзакции)

3. Unique Key (Уникальный ключ)

Определение: гарантирует, что значения в столбце(ах) уникальны, но может содержать NULL (в отличие от PRIMARY KEY).

CREATE TABLE users (
    user_id BIGINT PRIMARY KEY,
    email VARCHAR(255) UNIQUE NOT NULL,     -- UNIQUE ключ
    username VARCHAR(100) UNIQUE NOT NULL,  -- UNIQUE ключ
    phone VARCHAR(20),
    UNIQUE (phone)  -- Альтернативный синтаксис
);

-- Составной UNIQUE ключ
CREATE TABLE user_logins (
    login_id BIGINT PRIMARY KEY,
    user_id BIGINT,
    ip_address VARCHAR(45),
    UNIQUE (user_id, ip_address)  -- Комбинация должна быть уникальна
);

-- Несколько NULL допускаются
INSERT INTO user_logins (user_id, ip_address) VALUES (1, NULL);
INSERT INTO user_logins (user_id, ip_address) VALUES (1, NULL);  -- OK!

4. Candidate Key (Кандидат на первичный ключ)

Определение: столбец(ы), который может служить первичным ключом. В таблице может быть несколько кандидатов, но только один выбирается как PRIMARY KEY.

-- Все три столбца — candidate keys
CREATE TABLE products (
    product_id BIGINT PRIMARY KEY,           -- Выбранный PRIMARY KEY
    sku VARCHAR(100) UNIQUE,                 -- Candidate key #1
    barcode VARCHAR(100) UNIQUE,             -- Candidate key #2
    name VARCHAR(255) NOT NULL
);

-- Проверка: каждый candidate key должен быть уникален
INSERT INTO products (product_id, sku, barcode)
VALUES (1, 'SKU-001', 'BARCODE-001');

INSERT INTO products (product_id, sku, barcode)
VALUES (2, 'SKU-001', 'BARCODE-002');  -- ERROR! SKU дублируется

5. Super Key (Суперключ)

Определение: любой набор столбцов, который может уникально идентифицировать записи. Может содержать лишние столбцы.

-- Примеры super keys для таблицы users:
-- (user_id)              — minimal super key (PRIMARY KEY)
-- (email)                — minimal super key
-- (user_id, email)       — super key (содержит PRIMARY KEY)
-- (user_id, email, name) — super key (содержит PRIMARY KEY)
-- (email, phone)         — super key

6. Composite Key (Составной ключ)

Определение: первичный ключ, состоящий из нескольких столбцов.

CREATE TABLE user_roles (
    user_id BIGINT,
    role_id INT,
    assigned_at TIMESTAMP,
    PRIMARY KEY (user_id, role_id)  -- Составной ключ
);

-- Позволяет одного пользователя назначить на несколько ролей
INSERT INTO user_roles (user_id, role_id, assigned_at)
VALUES 
    (1, 10, NOW()),  -- Пользователь 1, роль 10
    (1, 20, NOW());  -- Пользователь 1, роль 20 — OK!

-- Но одного пользователя нельзя дважды назначить на одну роль
INSERT INTO user_roles (user_id, role_id, assigned_at)
VALUES (1, 10, NOW());  -- ERROR! (1, 10) уже существует

-- Составной ключ в WHERE
DELETE FROM user_roles
WHERE user_id = 1 AND role_id = 10;  -- Удалить конкретную пару

7. Natural Key vs Surrogate Key

Natural Key — натуральный идентификатор, используется в бизнесе

CREATE TABLE departments (
    dept_code VARCHAR(10) PRIMARY KEY,  -- Natural key (используется в бизнесе)
    dept_name VARCHAR(100),
    budget DECIMAL(10, 2)
);

SELECT * FROM departments WHERE dept_code = 'ENG';  -- Логично

Surrogate Key — искусственный идентификатор, генерируется БД

CREATE TABLE departments (
    dept_id BIGINT PRIMARY KEY,                    -- Surrogate key (авто-генерируется)
    dept_code VARCHAR(10) UNIQUE NOT NULL,         -- Natural key (бизнес-идентификатор)
    dept_name VARCHAR(100),
    budget DECIMAL(10, 2)
);

-- Автогенерация
INSERT INTO departments (dept_code, dept_name, budget)
VALUES ('ENG', 'Engineering', 1000000);
-- dept_id генерируется автоматически

Когда использовать:

• Natural Key: для малых, стабильных идентификаторов (country_code, product_sku)
• Surrogate Key: для больших таблиц, нестабильных идентификаторов

8. Distributed Keys (в NoSQL и аналитике)

Partition Key — разделяет данные между узлами

# Cassandra пример
CREATE TABLE users (
    user_id BIGINT,
    email VARCHAR,
    name VARCHAR,
    PRIMARY KEY (user_id)  -- Partition key
) WITH CLUSTERING ORDER BY (email DESC);

# DynamoDB пример
# HASH key = partition key (распределяет данные)
# RANGE key = sort key (сортирует внутри partition)

SQL примеры работы с ключами

Проверка уникальности перед вставкой

-- Проверить дубликаты
SELECT email, COUNT(*) as count
FROM users
GROUP BY email
HAVING COUNT(*) > 1;

-- Найти записи с нарушением FK
SELECT o.order_id, o.user_id
FROM orders o
LEFT JOIN users u ON o.user_id = u.user_id
WHERE u.user_id IS NULL;  -- Orders с несуществующим user_id

Добавить ключ к существующей таблице

-- Добавить UNIQUE ключ
ALTER TABLE users
ADD CONSTRAINT unique_email UNIQUE (email);

-- Добавить FOREIGN KEY
ALTER TABLE orders
ADD CONSTRAINT fk_orders_users
FOREIGN KEY (user_id) REFERENCES users(user_id);

-- Удалить ключ
ALTER TABLE users
DROP CONSTRAINT unique_email;

Best Practices

1. Всегда имейте PRIMARY KEY

-- Плохо
CREATE TABLE logs (
    timestamp TIMESTAMP,
    message TEXT
);

-- Хорошо
CREATE TABLE logs (
    log_id BIGINT PRIMARY KEY,
    timestamp TIMESTAMP,
    message TEXT
);

2. Используйте BIGINT для ID в больших таблицах

-- Для таблиц >1B записей
CREATE TABLE events (
    event_id BIGINT PRIMARY KEY,  -- BIGINT вместо INT (2B vs 4B)
    user_id BIGINT,
    timestamp TIMESTAMP
);

3. Индексируйте Foreign Keys

CREATE TABLE orders (
    order_id BIGINT PRIMARY KEY,
    user_id BIGINT,
    FOREIGN KEY (user_id) REFERENCES users(user_id),
    INDEX idx_orders_user_id (user_id)  -- Нужен для быстрых JOIN
);

4. Документируйте relationships

-- Используйте диаграммы ER (Entity-Relationship)
-- users (1) ──→ (N) orders
-- users (1) ──→ (N) user_roles
-- roles (1) ←─ (N) user_roles

Правильный выбор и использование ключей обеспечивает целостность, производительность и масштабируемость БД.