Приведи пример использования составного первичного ключа в БД

Составной первичный ключ в БД

Композитный PK (Primary Key) — это ключ, состоящий из нескольких столбцов. Используется когда уникальность требует комбинации полей.

Пример 1: Junction Table (Many-to-Many)

Сценарий: Студент может записаться на несколько курсов, курс может иметь много студентов.

CREATE TABLE students (
  id UUID PRIMARY KEY,
  name VARCHAR(100) NOT NULL
);

CREATE TABLE courses (
  id UUID PRIMARY KEY,
  title VARCHAR(100) NOT NULL
);

CREATE TABLE student_courses (
  student_id UUID NOT NULL,
  course_id UUID NOT NULL,
  enrolled_at TIMESTAMP DEFAULT NOW(),
  PRIMARY KEY (student_id, course_id), -- Составной ключ
  FOREIGN KEY (student_id) REFERENCES students(id),
  FOREIGN KEY (course_id) REFERENCES courses(id)
);

-- Невозможно записать одного студента на один курс дважды
INSERT INTO student_courses (student_id, course_id) VALUES ('abc', 'course1');
INSERT INTO student_courses (student_id, course_id) VALUES ('abc', 'course1'); -- ОШИБКА!

Node.js TypeORM:

@Entity('students')
export class Student {
  @PrimaryGeneratedColumn('uuid')
  id: string;

  @Column()
  name: string;

  @ManyToMany(() => Course)
  @JoinTable({
    name: 'student_courses',
    joinColumn: { name: 'student_id' },
    inverseJoinColumn: { name: 'course_id' },
  })
  courses: Course[];
}

@Entity('courses')
export class Course {
  @PrimaryGeneratedColumn('uuid')
  id: string;

  @Column()
  title: string;
}

Пример 2: Order Items

Сценарий: Один заказ может содержать одну единицу товара максимум (нет дублей).

CREATE TABLE orders (
  id UUID PRIMARY KEY,
  user_id UUID NOT NULL,
  created_at TIMESTAMP DEFAULT NOW()
);

CREATE TABLE order_items (
  order_id UUID NOT NULL,
  product_id UUID NOT NULL,
  quantity INT NOT NULL,
  price DECIMAL(10, 2) NOT NULL,
  PRIMARY KEY (order_id, product_id), -- Составной ключ
  FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE
);

-- Один заказ может иметь только один товар определённого типа
INSERT INTO order_items VALUES ('ord1', 'prod1', 2, 50.00);
INSERT INTO order_items VALUES ('ord1', 'prod1', 3, 50.00); -- ОШИБКА: дубликат!

Node.js Запрос:

async function getOrderWithItems(orderId: string) {
  const order = await db.query(`
    SELECT o.id, o.created_at, 
           json_agg(json_build_object(
             'product_id', oi.product_id,
             'quantity', oi.quantity,
             'price', oi.price
           )) as items
    FROM orders o
    LEFT JOIN order_items oi ON o.id = oi.order_id
    WHERE o.id = $1
    GROUP BY o.id
  `, [orderId]);
  return order.rows[0];
}

Пример 3: User Permissions

Сценарий: Пользователь может иметь роль на определённом уровне (проект, команда), но только одну.

CREATE TABLE users (
  id UUID PRIMARY KEY,
  email VARCHAR(100) UNIQUE NOT NULL
);

CREATE TABLE teams (
  id UUID PRIMARY KEY,
  name VARCHAR(100) NOT NULL
);

CREATE TABLE team_members (
  user_id UUID NOT NULL,
  team_id UUID NOT NULL,
  role VARCHAR(50) NOT NULL CHECK (role IN ('admin', 'member', 'viewer')),
  joined_at TIMESTAMP DEFAULT NOW(),
  PRIMARY KEY (user_id, team_id), -- Составной ключ
  FOREIGN KEY (user_id) REFERENCES users(id),
  FOREIGN KEY (team_id) REFERENCES teams(id)
);

-- Пользователь может быть в команде только один раз
INSERT INTO team_members VALUES ('user1', 'team1', 'admin');
INSERT INTO team_members VALUES ('user1', 'team1', 'member'); -- ОШИБКА!

Пример 4: Product Variants (SKU)

Сценарий: Один товар может иметь варианты (размеры, цвета), каждый вариант уникален.

CREATE TABLE products (
  id UUID PRIMARY KEY,
  name VARCHAR(100) NOT NULL
);

CREATE TABLE product_variants (
  product_id UUID NOT NULL,
  size VARCHAR(10) NOT NULL,
  color VARCHAR(50) NOT NULL,
  sku VARCHAR(50) UNIQUE NOT NULL, -- SKU должен быть уникален
  stock INT DEFAULT 0,
  price DECIMAL(10, 2) NOT NULL,
  PRIMARY KEY (product_id, size, color), -- Составной ключ (3 столбца)
  FOREIGN KEY (product_id) REFERENCES products(id)
);

-- Один товар не может иметь одну комбинацию (размер + цвет) дважды
INSERT INTO product_variants VALUES ('prod1', 'M', 'red', 'SKU-001', 10, 29.99);
INSERT INTO product_variants VALUES ('prod1', 'M', 'red', 'SKU-002', 5, 29.99); -- ОШИБКА!

Node.js запрос:

async function getProductVariants(productId: string) {
  return db.query(`
    SELECT product_id, size, color, sku, stock, price
    FROM product_variants
    WHERE product_id = $1
    ORDER BY size, color
  `, [productId]);
}

Пример 5: Sharded Data by Time and User

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

-- Таблица для каждого дня (шардирование по времени + пользователю)
CREATE TABLE user_events_2024_01_01 (
  user_id UUID NOT NULL,
  event_id UUID NOT NULL,
  timestamp TIMESTAMP NOT NULL,
  event_type VARCHAR(50) NOT NULL,
  data JSONB,
  PRIMARY KEY (user_id, event_id), -- Составной ключ
  FOREIGN KEY (user_id) REFERENCES users(id)
);

-- Для каждого дня новая таблица
-- user_events_2024_01_02
-- user_events_2024_01_03
-- etc.

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

✅ Используй составной PK когда:

• Many-to-many отношения
• Уникальность требует комбинации полей
• Хочешь явно заявить об этом в структуре БД

❌ Не используй когда:

• Есть естественный UUID/ID (используй его)
• Нужна гибкость изменения уникальных полей
• Слишком много столбцов в ключе (максимум 3-4)

Индексирование составного ключа

-- Автоматически создается индекс на PRIMARY KEY
-- Но часто нужны дополнительные для поиска

CREATE INDEX idx_student_courses_course 
  ON student_courses(course_id); -- Поиск по курсу

CREATE INDEX idx_order_items_product 
  ON order_items(product_id, order_id); -- Поиск товаров