Как обеспечивается уникальность записей и устранение дубликатов на уровне базы данных?

Question

Уникальность записей обеспечивается с помощью ограничений уникальности (UNIQUE constraints) и первичных ключей (PRIMARY KEY). При попытке вставить дублирующую запись СУБД автоматически отклоняет операцию с ошибкой. Для устранения существующих дубликатов используются запросы с оконными функциями или временные таблицы. Индексы уникальности создаются автоматически для ускорения проверки. Также можно использовать частичные уникальные индексы для условной уникальности

YeaHub · Accepted Answer

Базы данных предоставляют несколько механизмов для гарантии уникальности данных.1. Механизмы обеспечения уникальностиПервичный ключ (PRIMARY KEY): Гарантирует уникальность и не позволяет NULL значенияОграничение уникальности (UNIQUE constraint): Обеспечивает уникальность, но разрешает NULLУникальные индексы: Создают индекс, который обеспечивает уникальность значений2. Создание ограничений уникальности-- Первичный ключ
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    email VARCHAR(255) UNIQUE NOT NULL,
    username VARCHAR(100) UNIQUE
);

-- Составной уникальный ключ
CREATE TABLE user_roles (
    user_id INT,
    role_id INT,
    PRIMARY KEY (user_id, role_id)
);

-- Частичный уникальный индекс (только для активных записей)
CREATE UNIQUE INDEX idx_unique_active_email 
ON users (email) WHERE is_active = true;3. Устранение существующих дубликатов-- Удаление дубликатов с сохранением одной записи
DELETE FROM users 
WHERE id NOT IN (
    SELECT MIN(id) 
    FROM users 
    GROUP BY email
);

-- Использование оконных функций (PostgreSQL)
DELETE FROM users 
WHERE id IN (
    SELECT id FROM (
        SELECT id, 
               ROW_NUMBER() OVER (PARTITION BY email ORDER BY id) as rn
        FROM users
    ) dups 
    WHERE dups.rn > 1
);4. Обработка ошибок уникальности в приложенииimport psycopg2
from psycopg2 import errors

def create_user(conn, email, username):
    try:
        with conn.cursor() as cur:
            cur.execute(
                "INSERT INTO users (email, username) VALUES (%s, %s)",
                (email, username)
            )
            conn.commit()
            return True
    except errors.UniqueViolation as e:
        conn.rollback()
        print(f"Пользователь с таким email или username уже существует: {e}")
        return FalseВывод: Используйте ограничения уникальности на уровне базы данных как основной механизм предотвращения дубликатов. Для устранения существующих дубликатов применяйте оконные функции или группировку. Всегда обрабатывайте возможные нарушения уникальности в коде приложения.