Какие знаешь ограничения уникальности в БД?

Ограничения уникальности в БД

Ограничения уникальности (uniqueness constraints) — это правила, которые гарантируют, что определённые значения в таблице не повторяются. Это фундаментальная часть интеграции данных и защиты от дубликатов.

1. PRIMARY KEY — главный уникальный ключ

Первичный ключ — это обязательный и уникальный идентификатор строки.

-- Простой PRIMARY KEY
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    email VARCHAR(255) NOT NULL,
    name VARCHAR(100)
);

-- Составной PRIMARY KEY (из нескольких колонок)
CREATE TABLE order_items (
    order_id INT,
    item_id INT,
    quantity INT,
    PRIMARY KEY (order_id, item_id)
);

В Python (SQLAlchemy):

from sqlalchemy import Column, Integer, String, create_engine
from sqlalchemy.orm import declarative_base

Base = declarative_base()

class User(Base):
    __tablename__ = "users"
    
    id = Column(Integer, primary_key=True)  # Автоматически UNIQUE
    email = Column(String(255), nullable=False)
    name = Column(String(100))

class OrderItem(Base):
    __tablename__ = "order_items"
    
    order_id = Column(Integer, primary_key=True)
    item_id = Column(Integer, primary_key=True)
    quantity = Column(Integer)
    
    __table_args__ = (
        ForeignKeyConstraint(['order_id'], ['orders.id']),
    )

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

• UNIQUE — значение не может повторяться
• NOT NULL — значение обязательно
• По одному на таблицу
• Используется для индексации и связей

2. UNIQUE CONSTRAINT — уникальность без обязательности

UNIQUE требует уникальности, но допускает NULL (и в SQL несколько NULL считаются разными).

-- Один UNIQUE constraint
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    email VARCHAR(255) UNIQUE NOT NULL,  -- Единственный email
    username VARCHAR(100) UNIQUE,        -- NULL может быть несколько
    name VARCHAR(100)
);

-- Составной UNIQUE (комбинация должна быть уникальна)
CREATE TABLE user_emails (
    user_id INT NOT NULL,
    email_address VARCHAR(255) NOT NULL,
    email_type VARCHAR(20),
    UNIQUE (user_id, email_type)  -- Один email каждого типа per user
);

В SQLAlchemy:

class User(Base):
    __tablename__ = "users"
    
    id = Column(Integer, primary_key=True)
    email = Column(String(255), unique=True, nullable=False)
    username = Column(String(100), unique=True)
    name = Column(String(100))

class UserEmail(Base):
    __tablename__ = "user_emails"
    
    id = Column(Integer, primary_key=True)
    user_id = Column(Integer, ForeignKey("users.id"), nullable=False)
    email_address = Column(String(255), nullable=False)
    email_type = Column(String(20))  # work, personal, other
    
    __table_args__ = (
        UniqueConstraint('user_id', 'email_type', name='uq_user_email_type'),
    )

Поведение с NULL:

-- Эти две строки разные (NULL != NULL)
INSERT INTO users (email, username) VALUES ('alice@example.com', NULL);
INSERT INTO users (email, username) VALUES ('bob@example.com', NULL);  -- OK!

-- Но это будет ошибкой (duplicate email)
INSERT INTO users (email, username) VALUES ('alice@example.com', 'alice');
-- ERROR: duplicate key value violates unique constraint

3. UNIQUE INDEX — UNIQUE через индекс

Уникальные индексы — это альтернатива UNIQUE constraint, дающая больше контроля.

-- Создать уникальный индекс
CREATE UNIQUE INDEX idx_users_email ON users(email);

-- Partial UNIQUE index (с условием)
CREATE UNIQUE INDEX idx_active_emails 
    ON users(email) 
    WHERE active = true;  -- Уникальность только для активных

-- Уникальный индекс на expression
CREATE UNIQUE INDEX idx_users_lower_email 
    ON users(LOWER(email));  -- Уникален по lowercase email

Когда это полезно:

# SQLAlchemy с custom index
from sqlalchemy import Index, func

class User(Base):
    __tablename__ = "users"
    
    id = Column(Integer, primary_key=True)
    email = Column(String(255), nullable=False)
    active = Column(Boolean, default=True)
    
    __table_args__ = (
        # Unique index на lowercase email
        Index('idx_lower_email', func.lower(email), unique=True),
    )

4. Обработка дубликатов при вставке

PostgreSQL: ON CONFLICT

-- Если конфликт — обновить
INSERT INTO users (id, email, name) 
VALUES (1, 'alice@example.com', 'Alice')
ON CONFLICT (id) DO UPDATE 
SET email = EXCLUDED.email, name = EXCLUDED.name;

-- Если конфликт — ничего не делать
INSERT INTO users (id, email, name) 
VALUES (1, 'alice@example.com', 'Alice')
ON CONFLICT (id) DO NOTHING;

SQLAlchemy:

from sqlalchemy import insert
from sqlalchemy.dialects.postgresql import insert as postgres_insert

# Upsert в PostgreSQL
stmt = postgres_insert(User).values(
    id=1,
    email='alice@example.com',
    name='Alice'
).on_conflict_do_update(
    index_elements=['id'],
    set_={'email': 'alice@example.com', 'name': 'Alice'}
)

session.execute(stmt)
session.commit()

MySQL: ON DUPLICATE KEY

INSERT INTO users (id, email, name) 
VALUES (1, 'alice@example.com', 'Alice')
ON DUPLICATE KEY UPDATE 
    email = VALUES(email),
    name = VALUES(name);

5. Глобальная уникальность (UUID)

Для распределённых систем часто используют UUID вместо SERIAL.

from uuid import uuid4
from sqlalchemy import String

class User(Base):
    __tablename__ = "users"
    
    id = Column(String(36), primary_key=True, default=lambda: str(uuid4()))
    email = Column(String(255), unique=True, nullable=False)
    name = Column(String(100))

# Гарантирует уникальность даже при репликации
user = User(
    email='alice@example.com',
    name='Alice'
)
# id будет автоматически сгенерирован как UUID

6. Обработка дубликатов в приложении

from sqlalchemy.exc import IntegrityError
from sqlalchemy.orm import Session

def create_user(db: Session, email: str, name: str):
    try:
        user = User(email=email, name=name)
        db.add(user)
        db.commit()
        db.refresh(user)
        return user
    except IntegrityError as e:
        db.rollback()
        
        if 'duplicate key' in str(e):
            # Email уже существует
            raise ValueError(f"User with email {email} already exists")
        else:
            raise

7. Условные уникальные ограничения

Иногда уникальность требуется только при определённых условиях.

-- PostgreSQL: partial UNIQUE constraint
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    sku VARCHAR(100),
    store_id INT,
    active BOOLEAN DEFAULT true,
    UNIQUE (sku, store_id) WHERE active = true
);

-- Разные неактивные SKU могут повторяться
INSERT INTO products (sku, store_id, active) VALUES ('ABC', 1, true);
INSERT INTO products (sku, store_id, active) VALUES ('ABC', 2, true);  -- OK (другой store)
INSERT INTO products (sku, store_id, active) VALUES ('ABC', 1, false); -- OK (неактивен)
INSERT INTO products (sku, store_id, active) VALUES ('ABC', 1, false); -- OK (неактивен)

8. Функциональные уникальные индексы

Уникальность на основе функции, не простого поля.

-- Уникален по lowercase email
CREATE UNIQUE INDEX idx_email_lower 
    ON users(LOWER(email));

-- Уникален по году рождения для каждого города
CREATE UNIQUE INDEX idx_birthyear_per_city 
    ON users(city_id, DATE_PART('year', birth_date));

-- Уникален по удаленным лидирующим/хвостовым пробелам
CREATE UNIQUE INDEX idx_trimmed_name 
    ON users(TRIM(name));

9. Race conditions при проверке уникальности

Проблема: проверяешь наличие, потом вставляешь — между проверкой и вставкой может вставиться другой процесс.

# ❌ ПЛОХО - race condition
if not session.query(User).filter_by(email=email).exists():
    user = User(email=email, name=name)
    session.add(user)
    session.commit()
    # Между exists() и add() может вставиться другой процесс!

# ✅ ХОРОШО - используй constraint + обработка ошибки
try:
    user = User(email=email, name=name)
    session.add(user)
    session.commit()
except IntegrityError:
    session.rollback()
    raise ValueError("Email already exists")

10. Проверка существующих ограничений

-- В PostgreSQL
SELECT constraint_name, constraint_type 
FROM information_schema.table_constraints 
WHERE table_name = 'users';

-- Информация об индексах
SELECT indexname, indexdef 
FROM pg_indexes 
WHERE tablename = 'users';

Сравнение подходов

Подход                  | Преимущества                | Недостатки
------------------------|-----------------------------|-----------------------
PRIMARY KEY            | Обязательный, индексируется | Только один, NOT NULL
UNIQUE                 | Простой, допускает NULL     | Медленнее для индекса
UNIQUE INDEX           | Контроль, функции           | Сложнее в управлении
ON CONFLICT (Pg)       | Элегантно при дубликатах    | Не все БД поддерживают
Проверка в коде        | Гибкость, custom логика     | Медленнее, race conditions

Best Practices

1. Всегда используй PRIMARY KEY или UNIQUE на идентификаторы

class User(Base):
    id = Column(Integer, primary_key=True)
    email = Column(String(255), unique=True, nullable=False)

1. Для email/username используй уникальные ограничения

__table_args__ = (
    UniqueConstraint('email', name='uq_email'),
    UniqueConstraint('username', name='uq_username'),
)

1. Обрабатывай IntegrityError в приложении

try:
    db.commit()
except IntegrityError:
    db.rollback()
    # Custom handling

1. Используй ON CONFLICT для больших объёмов

ON CONFLICT (id) DO UPDATE SET ...

1. Документируй случаи, где NULL может быть несколько

# optional_field может быть NULL у нескольких записей
optional_field = Column(String(255), unique=True, nullable=True)