Уникальность комбинации двух столбцов в PostgreSQL: SQL-запрос

Быстрый ответ

Для гарантии уникальности комбинации двух колонок в PostgreSQL используйте ограничение уникальности:

ALTER TABLE ваша_таблица ADD CONSTRAINT уникальность_col1_col2 UNIQUE (колонка1, колонка2);

Это ограничение предотвращает дублирование пар значений в выбранных колонках.

Если обе колонки всегда заполнены, рассмотрите возможность создания составного первичного ключа:

ALTER TABLE ваша_таблица ADD PRIMARY KEY (колонка1, колонка2);

Начиная с версии 10 PostgreSQL, для добавления автоинкрементных колонок рекомендуется использовать GENERATED ALWAYS AS IDENTITY, вместо serial:

ALTER TABLE ваша_таблица ADD COLUMN новое_id INTEGER GENERATED ALWAYS AS IDENTITY;

Проверьте, что колонки, входящие в ограничение уникальности, не содержат NULL, чтобы избежать путаницы с пониманием уникальности:

ALTER TABLE ваша_таблица ALTER COLUMN колонка1 SET NOT NULL;
ALTER TABLE ваша_таблица ALTER COLUMN колонка2 SET NOT NULL;

Безопасное изменение схемы с помощью миграций

При изменении схемы базы данных рекомендуется использовать миграции "вверх" и "вниз" для гарантии безопасности процесса:

• Миграция "вверх" создает или изменяет схему.
• Миграция "вниз" выполняет откат изменений в случае ошибки или нештатной ситуации.

Такой подход позволяет выстроить надежный процесс инкрементной разработки схемы базы данных с минимизацией риска для данных.

Изменение ограничений и колонок: будьте осторожны

Для удаления ограничения уникальности или целой колонки используйте следующие SQL-команды:

ALTER TABLE ваша_таблица DROP CONSTRAINT уникальность_col1_col2; -- в случае ошибки при настройке
ALTER TABLE ваша_таблица DROP COLUMN имя_колонки; -- если колонка стала неактуальной

Применяйте их с пониманием возможных рисков для работы приложения и целостности данных.

Защита целостности данных

Составное ограничение уникальности обеспечивает уникальность каждой пары значений:

CREATE TABLE пример (
    id INTEGER,
    колонка1 INTEGER NOT NULL,
    колонка2 INTEGER NOT NULL,
    UNIQUE (колонка1, колонка2) -- Запрет на дублирование.
);

Подробнее о DDL-ограничениях можно узнать из официальной документации PostgreSQL:

Документация PostgreSQL.

Визуализация

Представьте уникальность комбинации двух колонок как специфический замок:

🔒 Уникальный замок: (🔑 Колонка А, 🔑 Колонка Б)

Каждый уникальный ключ подходит для этого замка:

🔒🔑🧩: [A1+B1, A2+B2, A3+B3] // В образе – уникальность.

Дубликаты к замку не подходят:

❌ [A1+B1, A1+B2, A1+B1] // Последний ключ – копия, замок остается закрытым.

PostgreSQL отсекает записи, нарушающие ограничение уникальности.

Тактичная обработка ограничений

При нарушении ограничений следует обрабатывать исключения тактично и информировать пользователя о конфликтах с дублирующимися данными.

Существуют различные стратегии обработки конфликтов:

• Предотвращение конфликтов: проверка данных перед добавлением.
• Реагирование на конфликты: использование ON CONFLICT для устранения ошибок при вставке данных.

INSERT INTO ваша_таблица (колонка1, колонка2) VALUES (значение1, значение2)
ON CONFLICT ON CONSTRAINT уникальность_col1_col2 DO NOTHING; 
// SQL-конфликты обрабатываются без ошибок.

Продвинутое использование уникальных ограничений

Уникальные ограничения можно эксплуатировать с пользой для вашей системы:

• Для создания Составных Ключей: обеспечивая уникальность комбинаций значений нескольких колонок.
• В Бизнес-логике: гарантируя выполнение сложных требований к уникальности.
• Для Производительности запросов: использование уникальных индексов для ускорения поиска.

Полезные материалы

1. PostgreSQL: Документация: 16: CREATE TABLE – Управление SQL и ограничениями в официальной документации.
2. Exclusion Constraints – PostgreSQL wiki – Когда ограничения исключения могут быть полезнее.
3. Pull requests · postgres/postgres · GitHub — Внести вклад в развитие PostgreSQL и обсудить пакеты обновлений, касающиеся ограничений.