В чем разница между Primary и Foreign Key?

Primary Key vs Foreign Key

Определение

Primary Key (Первичный ключ) — уникальный идентификатор записи в таблице

Foreign Key (Внешний ключ) — ссылка на Primary Key в другой таблице

Primary Key

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

• Уникально идентифицирует запись
• Не может быть NULL
• Может быть только один в таблице
• Обычно числовой (AUTO_INCREMENT)
• Индексируется автоматически

Пример:

CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100),
    email VARCHAR(100) UNIQUE
);

Foreign Key

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

• Ссылается на Primary Key другой таблицы
• Может быть NULL (если не задана NOT NULL)
• Может быть много в таблице
• Обеспечивает целостность данных
• Может быть NULL или совпадать с Primary Key в другой таблице

Пример:

CREATE TABLE orders (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    total DECIMAL(10, 2),
    FOREIGN KEY (user_id) REFERENCES users(id)
);

Диаграмма

Таблица users:
id (PK) | name      | email
1       | John      | john@test.com
2       | Jane      | jane@test.com
3       | Bob       | bob@test.com

Таблица orders:
id (PK) | user_id (FK) | total
1       | 1            | 100.00  (относится к John)
2       | 2            | 200.00  (относится к Jane)
3       | 1            | 50.00   (относится к John)

SQL примеры

Создание таблиц с ключами:

-- Таблица departments
CREATE TABLE departments (
    dept_id INT PRIMARY KEY AUTO_INCREMENT,
    dept_name VARCHAR(100) NOT NULL
);

-- Таблица employees
CREATE TABLE employees (
    emp_id INT PRIMARY KEY AUTO_INCREMENT,
    emp_name VARCHAR(100),
    dept_id INT,
    FOREIGN KEY (dept_id) REFERENCES departments(dept_id)
);

Вставка данных:

INSERT INTO departments VALUES (1, 'IT');
INSERT INTO departments VALUES (2, 'HR');

INSERT INTO employees VALUES (1, 'Alice', 1);
INSERT INTO employees VALUES (2, 'Bob', 2);
INSERT INTO employees VALUES (3, 'Charlie', 1);

Запросы с JOIN:

-- Найти все сотрудники и их отделы
SELECT e.emp_name, d.dept_name
FROM employees e
JOIN departments d ON e.dept_id = d.dept_id;

-- Результат:
-- Alice     | IT
-- Bob       | HR
-- Charlie   | IT

Целостность данных

Foreign Key обеспечивает:

1. Нельзя вставить невалидный dept_id

-- Это вызовет ошибку (dept_id=99 не существует)
INSERT INTO employees VALUES (4, 'David', 99);
-- Error: Foreign key constraint fails

1. Нельзя удалить используемый department

-- Это вызовет ошибку (есть сотрудники в IT)
DELETE FROM departments WHERE dept_id = 1;
-- Error: Cannot delete or update a parent row

Cascade Options

-- ON DELETE CASCADE — удалить зависимые записи
CREATE TABLE orders (
    id INT PRIMARY KEY,
    user_id INT,
    FOREIGN KEY (user_id) REFERENCES users(id)
        ON DELETE CASCADE
);

-- Если удалить пользователя, удалятся и его заказы
DELETE FROM users WHERE id = 1;  -- Все заказы пользователя тоже удалятся

Сравнение

Тестирование

import pytest
import sqlite3

def test_primary_key_uniqueness():
    conn = sqlite3.connect(':memory:')
    cursor = conn.cursor()
    
    cursor.execute('''
        CREATE TABLE users (
            id INTEGER PRIMARY KEY,
            name TEXT
        )
    ''')
    
    cursor.execute("INSERT INTO users VALUES (1, 'John')")
    
    # Попытка вставить дубликат
    with pytest.raises(sqlite3.IntegrityError):
        cursor.execute("INSERT INTO users VALUES (1, 'Jane')")

def test_foreign_key_constraint():
    conn = sqlite3.connect(':memory:')
    cursor = conn.cursor()
    
    cursor.execute('PRAGMA foreign_keys = ON')
    
    cursor.execute('''
        CREATE TABLE departments (
            id INTEGER PRIMARY KEY,
            name TEXT
        )
    ''')
    
    cursor.execute('''
        CREATE TABLE employees (
            id INTEGER PRIMARY KEY,
            name TEXT,
            dept_id INTEGER,
            FOREIGN KEY (dept_id) REFERENCES departments(id)
        )
    ''')
    
    # Попытка вставить несуществующий dept_id
    with pytest.raises(sqlite3.IntegrityError):
        cursor.execute("INSERT INTO employees VALUES (1, 'John', 999)")

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

1. Всегда определяйте Primary Key
2. Используйте Foreign Keys для обеспечения целостности
3. Выбирайте правильный тип данных (обычно INT)
4. Рассмотрите CASCADE опции для удаления
5. Индексируйте Foreign Keys для производительности

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