Уникальные индексы, PRIMARY KEY, UNIQUE-ограничения и их взаимосвязь.
Уникальные индексы — это не только про производительность, но и про целостность данных. Понимание связи индексов и ограничений критично для проектирования БД.
В PostgreSQL есть два способа обеспечить уникальность:
CREATE UNIQUE INDEX idx_users_email ON users(email);ALTER TABLE users ADD CONSTRAINT users_email_unique UNIQUE (email);Важно: UNIQUE-ограничение реализуется через уникальный индекс. PostgreSQL автоматически создаёт индекс для проверки уникальности.
PRIMARY KEY — это комбинация UNIQUE и NOT NULL.
-- При создании таблицы
CREATE TABLE users (
id INTEGER PRIMARY KEY,
email VARCHAR(255)
);
-- Или явно
CREATE TABLE users (
id INTEGER,
email VARCHAR(255),
PRIMARY KEY (id)
);
-- Добавление к существующей таблице
ALTER TABLE users ADD PRIMARY KEY (id);CREATE TABLE users (
id INTEGER PRIMARY KEY
);
-- PostgreSQL автоматически создаёт:
-- 1. Уникальный индекс по id
-- 2. NOT NULL ограничениеПроверка:
SELECT indexname, indexdef
FROM pg_indexes
WHERE tablename = 'users';
-- Результат:
-- users_pkey | CREATE UNIQUE INDEX users_pkey ON users USING btree (id)| Характеристика | PRIMARY KEY | UNIQUE |
|---|---|---|
| UNIQUE-ограничение | ✅ Да | ✅ Да |
| NOT NULL | ✅ Да | ❌ Нет (разрешает NULL) |
| Количество на таблицу | ✅ Только один | ✅ Несколько |
| Кластеризация по умолчанию | ✅ Да (в старых версиях) | ❌ Нет |
-- Уникальный индекс
CREATE UNIQUE INDEX idx_users_email ON users(email);
-- Уникальный индекс по нескольким колонкам
CREATE UNIQUE INDEX idx_users_email_active ON users(email, active);INSERT INTO users (email) VALUES ('test@example.com'); -- ✅
INSERT INTO users (email) VALUES ('test@example.com'); -- ❌ Ошибка!
-- ERROR: duplicate key value violates unique constraint "idx_users_email"CREATE UNIQUE INDEX idx_users_phone ON users(phone);
INSERT INTO users (phone) VALUES (NULL); -- ✅
INSERT INTO users (phone) VALUES (NULL); -- ✅ NULL ≠ NULL в SQL
INSERT INTO users (phone) VALUES ('+1234567890'); -- ✅
INSERT INTO users (phone) VALUES ('+1234567890'); -- ❌ Ошибка!Важно: В SQL NULL IS NOT DISTINCT FROM NULL ложно, поэтому уникальные индексы разрешают несколько NULL.
-- Уникальный email только для активных пользователей
CREATE UNIQUE INDEX idx_users_active_email ON users(email)
WHERE active = true;
-- Активные пользователи не могут иметь дубликаты email
INSERT INTO users (email, active) VALUES ('test@example.com', true); -- ✅
INSERT INTO users (email, active) VALUES ('test@example.com', true); -- ❌
-- Неактивные могут иметь дубликаты
INSERT INTO users (email, active) VALUES ('test@example.com', false); -- ✅
INSERT INTO users (email, active) VALUES ('test@example.com', false); -- ✅-- Уникальная пара (user_id, role)
CREATE UNIQUE INDEX idx_user_role ON user_roles(user_id, role);
-- Или через ограничение
ALTER TABLE user_roles ADD CONSTRAINT unique_user_role
UNIQUE (user_id, role);Результат:
INSERT INTO user_roles (user_id, role) VALUES (1, 'admin'); -- ✅
INSERT INTO user_roles (user_id, role) VALUES (1, 'editor'); -- ✅
INSERT INTO user_roles (user_id, role) VALUES (1, 'admin'); -- ❌ Дубликат!
INSERT INTO user_roles (user_id, role) VALUES (2, 'admin'); -- ✅ Другой user_idCREATE UNIQUE INDEX idx_pair ON table_name(a, b);
INSERT INTO table_name (a, b) VALUES (1, NULL); -- ✅
INSERT INTO table_name (a, b) VALUES (1, NULL); -- ✅ NULL ≠ NULL
INSERT INTO table_name (a, b) VALUES (NULL, 1); -- ✅
INSERT INTO table_name (a, b) VALUES (NULL, 1); -- ✅
INSERT INTO table_name (a, b) VALUES (NULL, NULL); -- ✅
INSERT INTO table_name (a, b) VALUES (NULL, NULL); -- ✅EXCLUDE — более мощное ограничение, чем UNIQUE.
ALTER TABLE table_name ADD EXCLUDE USING index_type (column WITH operator);CREATE TABLE rooms (
room_id INTEGER,
start_time TIMESTAMP,
end_time TIMESTAMP,
EXCLUDE USING GIST (
room_id WITH =,
tsrange(start_time, end_time) WITH &&
)
);Результат:
-- ✅ Бронирование комнаты 1
INSERT INTO rooms (room_id, start_time, end_time)
VALUES (1, '2026-01-01 10:00', '2026-01-01 12:00');
-- ✅ Другая комната, то же время
INSERT INTO rooms (room_id, start_time, end_time)
VALUES (2, '2026-01-01 10:00', '2026-01-01 12:00');
-- ❌ Пересечение времени в комнате 1
INSERT INTO rooms (room_id, start_time, end_time)
VALUES (1, '2026-01-01 11:00', '2026-01-01 13:00');
-- ERROR: conflicting key value violates exclusion constraint| Оператор | Описание | Тип индекса |
|---|---|---|
= | Равенство | B-дерево, GiST |
&& | Пересечение | GiST |
&<, &> | Слева/справа | GiST |
<<, >> | Строго слева/справа | GiST |
@> | Содержит | GiST |
<@ | Содержится в | GiST |
По умолчанию PostgreSQL проверяет ограничения сразу. Но можно отложить проверку до конца транзакции.
CREATE TABLE users (
id INTEGER PRIMARY KEY,
email VARCHAR(255) UNIQUE -- NOT DEFERRABLE по умолчанию
);
BEGIN;
INSERT INTO users (id, email) VALUES (1, 'test@example.com');
INSERT INTO users (id, email) VALUES (2, 'test@example.com'); -- ❌ Ошибка сразу!
COMMIT;CREATE TABLE users (
id INTEGER PRIMARY KEY,
email VARCHAR(255),
CONSTRAINT users_email_unique UNIQUE (email)
DEFERRABLE INITIALLY IMMEDIATE
);
BEGIN;
INSERT INTO users (id, email) VALUES (1, 'test@example.com');
INSERT INTO users (id, email) VALUES (2, 'test@example.com'); -- ❌ Ошибка сразу!
COMMIT;
-- Но можно отложить явно:
BEGIN;
SET CONSTRAINTS users_email_unique DEFERRED;
INSERT INTO users (id, email) VALUES (1, 'test@example.com');
INSERT INTO users (id, email) VALUES (2, 'test@example.com'); -- Пока ок
COMMIT; -- ❌ Ошибка при коммите!CREATE TABLE users (
id INTEGER PRIMARY KEY,
email VARCHAR(255),
CONSTRAINT users_email_unique UNIQUE (email)
DEFERRABLE INITIALLY DEFERRED -- Проверка в конце транзакции
);
BEGIN;
INSERT INTO users (id, email) VALUES (1, 'test@example.com');
INSERT INTO users (id, email) VALUES (2, 'test@example.com'); -- Пока ок
COMMIT; -- ❌ Ошибка при коммите!CREATE TABLE friend_pairs (
user_id1 INTEGER REFERENCES users(id),
user_id2 INTEGER REFERENCES users(id),
PRIMARY KEY (user_id1, user_id2),
CONSTRAINT no_duplicate_pairs
UNIQUE (user_id1, user_id2)
DEFERRABLE INITIALLY DEFERRED
);
-- Вставка взаимных друзей
BEGIN;
INSERT INTO friend_pairs VALUES (1, 2);
INSERT INTO friend_pairs VALUES (2, 1); -- Дубликат, но проверка позже
COMMIT; -- ❌ Ошибка!SELECT
indexname,
indexdef
FROM pg_indexes
WHERE tablename = 'users'
AND indexdef LIKE '%UNIQUE%';SELECT
conname as constraint_name,
contype as constraint_type, -- u = UNIQUE, p = PRIMARY KEY
condeferrable,
condeferred
FROM pg_constraint
WHERE conrelid = 'users'::regclass;-- Найти дубликаты email
SELECT email, COUNT(*) as cnt
FROM users
GROUP BY email
HAVING COUNT(*) > 1;
-- Найти строки с дубликатами
SELECT u.*
FROM users u
JOIN (
SELECT email
FROM users
GROUP BY email
HAVING COUNT(*) > 1
) dup ON u.email = dup.email;-- Если создан как индекс
DROP INDEX idx_users_email;
-- Если создан как ограничение
ALTER TABLE users DROP CONSTRAINT users_email_unique;ALTER TABLE users DROP CONSTRAINT users_pkey;
-- или
ALTER TABLE users DROP PRIMARY KEY; -- Синтаксис MySQL, не работает в PostgreSQL!-- Нельзя изменить существующее ограничение
-- Нужно удалить и создать заново
ALTER TABLE users DROP CONSTRAINT users_email_unique;
ALTER TABLE users ADD CONSTRAINT users_email_unique
UNIQUE (email) DEFERRABLE INITIALLY DEFERRED;При вставке/обновлении:
-- Без уникального индекса
INSERT INTO users (email) VALUES ('test@example.com');
-- ~0.1 ms
-- С уникальным индексом
CREATE UNIQUE INDEX idx_users_email ON users(email);
INSERT INTO users (email) VALUES ('test@example.com');
-- ~0.15 ms (на 50% медленнее из-за проверки)CREATE UNIQUE INDEX idx_composite ON table_name(a, b, c, d);Проверка уникальности требует поиска по всем колонкам. Чем больше колонок, тем медленнее вставка.
CREATE TABLE users (
id BIGSERIAL PRIMARY KEY,
email VARCHAR(255) NOT NULL,
username VARCHAR(100) NOT NULL,
CONSTRAINT users_email_unique UNIQUE (email),
CONSTRAINT users_username_unique UNIQUE (username)
);CREATE TABLE subscriptions (
user_id BIGINT REFERENCES users(id),
plan_id BIGINT REFERENCES plans(id),
started_at TIMESTAMP NOT NULL,
ended_at TIMESTAMP,
PRIMARY KEY (user_id, plan_id),
CONSTRAINT one_active_subscription CHECK (
ended_at IS NULL OR started_at < ended_at
)
);
-- Один активный план на пользователя
CREATE UNIQUE INDEX idx_one_active_plan ON subscriptions(user_id)
WHERE ended_at IS NULL;CREATE TABLE bookings (
id BIGSERIAL PRIMARY KEY,
resource_id INTEGER NOT NULL,
start_time TIMESTAMP NOT NULL,
end_time TIMESTAMP NOT NULL,
EXCLUDE USING GIST (
resource_id WITH =,
tsrange(start_time, end_time) WITH &&
)
);Вопросы ещё не добавлены
Вопросы для этой подтемы ещё не добавлены.