Ограничение Oracle UNIQUE допускает одно и то же значение в разных случаях

Question

У меня есть ограничение UNIQUE для столбца в таблице Oracle Database. Допускается сохранять одно и то же значение в нижнем и верхнем регистре.

Например; Сначала я вставляю M100 в столбец UNIQUE таблицы. Опять же, когда я попытался вставить M100, я увидел ошибку нарушения уникального ограничения.

Но когда я пытаюсь вставить m100, база данных принимает это и сохраняется в таблице.

Как я мог это решить. Мне нужно ограничить значение независимо от случая.

Я использую версию ниже

"Oracle Database 18c Express Edition Release 18.0.0.0.0 - Production
Version 18.4.0.0.0"

Вот Live SQL

Answer 1

Поскольку вы используете Oracle 18, вы можете использовать новые функции, такие как невидимые столбцы и виртуальные столбцы или collation:

Виртуальный невидимый столбец + уникальное ограничение: например, у вас есть таблица T со столбцом STR:

create table t(str varchar2(10));

Таким образом, вы можете добавить невидимый виртуальный столбец str_lower генерируется как lower(str):

alter table t add 
      str_lower varchar2(10) invisible generated always as (lower(str)) ;

Поскольку этот столбец невидимый и виртуальный, он не нарушит ваш существующий код. Теперь вы можете добавить unique ограничение на это:

alter table t add
      constraint t_str_unique_lower
         unique(str_lower) using index;

Тестирование:

SQL> insert into t values('M100');

1 row created.

SQL> insert into t values('m100');
insert into t values('m100')
*
ERROR at line 1:
ORA-00001: unique constraint (XTENDER.T_STR_UNIQUE_LOWER) violated

Кроме того, он позволяет легко находить значения по меньшему значению:

SQL> select * from t where str_lower='m100';

STR
----------
M100

SQL> select str,str_lower from t where str_lower='m100';

STR        STR_LOWER
---------- ----------
M100       m100

Как видите, он не возвращает столбец str_lower, если вы не указали его в списке выбора:

Другое возможное решение - указать collationдля вашего столбца, но для этого требуется установить параметр базы данных MAX_STRING_SIZE к EXTENDED, иначе вы получите ORA-43929: Collation cannot be specified if parameter MAX_STRING_SIZE=STANDARD is set.

alter table t modify str COLLATE BINARY_CI;
alter table t add constraint t_str_unique unique(str);

Подробнее об этом:https://oracle-base.com/articles/12c/column-level-collation-and-case-insensitive-database-12cr2

Answer 2

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

CREATE UNIQUE INDEX some_index_name
ON tablename(UPPER(column_name));

Answer 3

Оба предыдущих ответа хороши, но есть еще один вариант, который может иметь смысл в зависимости от бизнес-кейса. Иногда ваши данные нечувствительны к регистру (например, адреса электронной почты), поэтому всегда храните их в том же регистре, используя триггер.

Тогда уникальное ограничение будет работать И ваши данные станут чище. Пример такого триггера:

create or replace TRIGGER t_biu BEFORE
  INSERT OR UPDATE ON t
  FOR EACH ROW
DECLARE
BEGIN
  :NEW.column_name := UPPER(:NEW.column_name);
END t_biu;