UNIQUE в Oracle SQL — как задать уникальность

UNIQUE в Oracle SQL. Введение

Ограничение UNIQUE в Oracle SQL используется для того, чтобы значения в указанном столбце или комбинации столбцов были уникальными. Это значит, что дубликаты недопустимы. UNIQUE помогает сохранять целостность данных и предотвращает повторное внесение одинаковых значений. В этой статье ты найдёшь 50 практических примеров использования UNIQUE.

🖋️ Синтаксис ограничения UNIQUE

При создании таблицы:

CREATE TABLE users (
  user_id NUMBER,
  email VARCHAR2(100) UNIQUE
);

Альтернативный синтаксис:

CREATE TABLE users (
  user_id NUMBER,
  email VARCHAR2(100),
  CONSTRAINT uq_email UNIQUE (email)
);

Добавление ограничения к уже существующей таблице:

ALTER TABLE users ADD CONSTRAINT uq_username UNIQUE (username);

🔄 Где используется UNIQUE

• Уникальные email, логины, номера телефонов
• Идентификаторы, не являющиеся первичным ключом
• Комбинации столбцов (составной UNIQUE)
• Временные и архивные таблицы для контроля записей

📊 Примеры использования UNIQUE в Oracle SQL (45 примеров)

1. Уникальный email при создании таблицы:

CREATE TABLE clients (
  client_id NUMBER,
  email VARCHAR2(100) UNIQUE
);

2. Добавление ограничения в ALTER:

ALTER TABLE clients ADD CONSTRAINT uq_email UNIQUE (email);

3. Проверка ограничения через ALL_CONSTRAINTS:

SELECT constraint_name, table_name FROM user_constraints
WHERE constraint_type = 'U';

4. Уникальное ограничение на два столбца:

ALTER TABLE orders ADD CONSTRAINT uq_order_client UNIQUE (order_id, client_id);

5. Ошибка при вставке дубликата:

INSERT INTO clients (email) VALUES ('test@mail.com');
-- Повторный INSERT вызовет ORA-00001

6. Создание таблицы с несколькими UNIQUE:

CREATE TABLE accounts (
  id NUMBER,
  username VARCHAR2(50) UNIQUE,
  email VARCHAR2(100) UNIQUE
);

7. Вставка уникального значения:

INSERT INTO clients (client_id, email) VALUES (1, 'a@b.com');

8. Попытка дубликата вызывает ошибку:

-- Это вызовет ORA-00001, если 'a@b.com' уже есть
INSERT INTO clients (client_id, email) VALUES (2, 'a@b.com');

9. UNIQUE с именем ограничения:

CREATE TABLE products (
  id NUMBER,
  sku VARCHAR2(20),
  CONSTRAINT uq_sku UNIQUE (sku)
);

10. UNIQUE в CREATE TABLE AS SELECT:

CREATE TABLE copy_clients AS SELECT * FROM clients WHERE 1=0;
ALTER TABLE copy_clients ADD CONSTRAINT uq_copy_email UNIQUE (email);

11. Проверка наличия уникального ограничения:

SELECT column_name FROM user_cons_columns
WHERE constraint_name = 'UQ_EMAIL';

12. Использование составного UNIQUE:

CREATE TABLE seats (
  row_num NUMBER,
  seat_num NUMBER,
  CONSTRAINT uq_seat UNIQUE (row_num, seat_num)
);

13. Удаление ограничения UNIQUE:

ALTER TABLE clients DROP CONSTRAINT uq_email;

14. Проверка через DBA_CONSTRAINTS:

SELECT * FROM dba_constraints WHERE constraint_type = 'U';

15. Уникальность через ALL_CONS_COLUMNS:

SELECT * FROM all_cons_columns WHERE constraint_name = 'UQ_SEAT';

16. Слияние таблицы с проверкой UNIQUE:

MERGE INTO clients c
USING dual
ON (c.email = 'unique@domain.com')
WHEN NOT MATCHED THEN
  INSERT (client_id, email) VALUES (3, 'unique@domain.com');

17. Вставка с проверкой EXISTS:

BEGIN
  IF NOT EXISTS (SELECT 1 FROM clients WHERE email = 'new@site.com') THEN
    INSERT INTO clients (email) VALUES ('new@site.com');
  END IF;
END;

18. Уникальность с IGNORE_ROW_ON_DUPKEY_INDEX:

INSERT /*+ IGNORE_ROW_ON_DUPKEY_INDEX(clients(email)) */
INTO clients (email) VALUES ('test@site.com');

19. UNIQUE в подзапросе (создание временной таблицы):

CREATE TABLE temp_users (
  id NUMBER,
  login VARCHAR2(30) UNIQUE
);

20. Уникальность и логика в представлении:

CREATE VIEW v_clients AS
SELECT DISTINCT email FROM clients;

21. Обработка ошибки ORA-00001:

BEGIN
  INSERT INTO clients (email) VALUES ('duplicate@domain.com');
EXCEPTION
  WHEN DUP_VAL_ON_INDEX THEN
    DBMS_OUTPUT.PUT_LINE('Дубликат!');
END;

22. Использование в PL/SQL процедуры:

PROCEDURE add_user(p_email VARCHAR2) IS
BEGIN
  INSERT INTO clients (email) VALUES (p_email);
EXCEPTION
  WHEN DUP_VAL_ON_INDEX THEN NULL;
END;

23. Проверка наличия значения перед вставкой:

IF NOT EXISTS (SELECT 1 FROM clients WHERE email = 'mail@host.com') THEN
  INSERT INTO clients (email) VALUES ('mail@host.com');
END IF;

24. Уникальность с NULL (NULL считается уникальным):

INSERT INTO clients (email) VALUES (NULL); -- допустимо
INSERT INTO clients (email) VALUES (NULL); -- также допустимо

25. Сравнение с PRIMARY KEY:

-- PRIMARY KEY = NOT NULL + UNIQUE
-- UNIQUE = допускает NULL

26. Создание временной таблицы с UNIQUE:

CREATE GLOBAL TEMPORARY TABLE tmp_users (
  id NUMBER,
  username VARCHAR2(30) UNIQUE
) ON COMMIT DELETE ROWS;

27. Создание MATERIALIZED VIEW с UNIQUE:

CREATE MATERIALIZED VIEW mv_clients
BUILD IMMEDIATE AS SELECT DISTINCT email FROM clients;

28. Использование UNIQUE в логике авторизации:

SELECT COUNT(*) FROM users WHERE login = 'john'; -- ожидается 0 или 1

29. Уникальность через ASSERT:

-- Псевдокод
ASSERT(SELECT COUNT(*) FROM users WHERE email = 'x') <= 1;

30. Уникальность с внешним ключом:

CREATE TABLE profiles (
  profile_id NUMBER PRIMARY KEY,
  user_id NUMBER,
  CONSTRAINT uq_profile_user UNIQUE (user_id)
);

31. Проверка уникального индекса:

SELECT * FROM user_indexes WHERE uniqueness = 'UNIQUE';

32. Имя ограничения и индекс:

-- Oracle создаёт индекс под капотом
-- Имя можно задать вручную

33. Автоматическая генерация уникального логина:

INSERT INTO users (login)
SELECT 'user_' || TO_CHAR(SYSDATE, 'HH24MISS') FROM dual
WHERE NOT EXISTS (
  SELECT 1 FROM users WHERE login = 'user_' || TO_CHAR(SYSDATE, 'HH24MISS')
);

34. Использование CREATE INDEX для UNIQUE:

CREATE UNIQUE INDEX idx_unique_email ON clients (email);

35. Импорт данных с проверкой UNIQUE:

-- Через внешние таблицы или SQL*Loader с контрольным флагом

36. Использование в тестировании:

BEGIN
  EXECUTE IMMEDIATE 'INSERT INTO test_table (val) VALUES (''unique'')';
EXCEPTION
  WHEN OTHERS THEN NULL;
END;

37. Отображение всех уникальных ограничений:

SELECT * FROM user_constraints WHERE constraint_type = 'U';

38. UNIQUE в фильтре EXISTS:

SELECT * FROM clients c
WHERE EXISTS (
  SELECT 1 FROM dual WHERE c.email IS NOT NULL
);

39. Уникальность через NOT EXISTS:

INSERT INTO clients (email)
SELECT 'x@domain.com' FROM dual
WHERE NOT EXISTS (SELECT 1 FROM clients WHERE email = 'x@domain.com');

40. Проверка количества уникальных email:

SELECT COUNT(DISTINCT email) FROM clients;

41. Вложенный SELECT с UNIQUE:

SELECT * FROM (
  SELECT DISTINCT email FROM clients
) WHERE ROWNUM <= 10;

42. Удаление дубликатов:

DELETE FROM clients c
WHERE ROWID NOT IN (
  SELECT MIN(ROWID) FROM clients GROUP BY email
);

43. Преобразование таблицы с дубликатами в уникальную:

CREATE TABLE clients_clean AS
SELECT email FROM clients GROUP BY email;

44. Защита логики авторизации:

-- Один email = один пользователь
-- UNIQUE обязательно

45. Создание уникальной последовательности действий:

INSERT INTO action_log (action_id, email)
VALUES (action_seq.NEXTVAL, 'unique@domain.com');

📆 Заключение: зачем использовать UNIQUE в Oracle SQL

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

Применяя его, ты гарантируешь, что важные атрибуты (email, код, логин) будут уникальны, как и должны быть в реальности.

🕸️ Следующая статья:

UID в Oracle SQL — как получить идентификатор пользователя