Я имел, это подходит пару раз в моей карьере, и ни один из моих локальных узлов, кажется, не может ответить на это. Скажите, что у меня есть таблица, которая имеет поле "Description", которое является ключом-кандидатом, за исключением того, что иногда пользователь будет останавливаться на полпути посредством процесса. Таким образом для, возможно, 25% записей это значение является нулевым, но для всего, что не является ПУСТЫМ, это должно быть уникально.
Другим примером могла бы быть таблица, которая должна поддержать несколько "версий" записи, и немного значения указывает, какой является "активным". Таким образом, "ключ-кандидат" всегда заполняется, но может быть три версии, которые идентичны (с 0 в активном бите) и только один, который активен (1 в активном бите).
У меня есть альтернативные методы для решения этих проблем (в первом случае, чтобы осуществить код правила, или в хранимой процедуре или в бизнес-слое, и во втором, заполнить архивную таблицу с триггером и ОБЪЕДИНЕНИЕМ таблицы, когда мне нужна история). Я не хочу альтернативы (если нет очевидно лучшие решения), я просто задаюсь вопросом, может ли какая-либо разновидность SQL выразить "условную уникальность" таким образом. Я использую MS SQL, поэтому если существует способ сделать это в этом, большом. Я главным образом просто академически заинтересован проблемой.
Если вы используете SQL Server 2008, фильтр индекса может быть вашим решением:
http://msdn.microsoft.com/en-us/library/ms188783.aspx
Вот как я применяю уникальный индекс с несколькими значениями NULL
CREATE UNIQUE INDEX [IDX_Blah] ON [tblBlah] ([MyCol]) WHERE [MyCol] IS NOT NULL
Что касается описаний, которые еще не завершены, у меня не было бы их в той же таблице, что и окончательные описания. Окончательная таблица будет иметь уникальный индекс или первичный ключ в описании.
В случае активного / неактивного, опять же, у меня могут быть отдельные таблицы, как у вас с таблицей «архив» или «история», но еще один возможный способ сделать это в MS SQL Server, по крайней мере, - использовать индексированное представление:
CREATE TABLE Test_Conditionally_Unique
(
my_id INT NOT NULL,
active BIT NOT NULL DEFAULT 0
)
GO
CREATE VIEW dbo.Test_Conditionally_Unique_View
WITH SCHEMABINDING
AS
SELECT
my_id
FROM
dbo.Test_Conditionally_Unique
WHERE
active = 1
GO
CREATE UNIQUE CLUSTERED INDEX IDX1 ON Test_Conditionally_Unique_View (my_id)
GO
INSERT INTO dbo.Test_Conditionally_Unique (my_id, active)
VALUES (1, 0)
INSERT INTO dbo.Test_Conditionally_Unique (my_id, active)
VALUES (1, 0)
INSERT INTO dbo.Test_Conditionally_Unique (my_id, active)
VALUES (1, 0)
INSERT INTO dbo.Test_Conditionally_Unique (my_id, active)
VALUES (1, 1)
INSERT INTO dbo.Test_Conditionally_Unique (my_id, active)
VALUES (2, 0)
INSERT INTO dbo.Test_Conditionally_Unique (my_id, active)
VALUES (2, 1)
INSERT INTO dbo.Test_Conditionally_Unique (my_id, active)
VALUES (2, 1) -- This insert will fail
Вы также можете использовать этот же метод для описаний NULL / Valued.
Я не совсем понимаю ваше предполагаемое использование или ваши таблицы, но вы можете попробовать использовать отношения один к одному. Выделите этот "иногда" уникальный столбец в новую таблицу, создайте индекс UNIQUE на этом столбце в новой таблице и FK обратно в исходную таблицу, используя PK исходной таблицы. В новой таблице будет строка только тогда, когда "уникальные" данные должны существовать.
Старые таблицы:
TableA
ID pk
Col1 sometimes unique
Col...
Новые таблицы:
TableA
ID
Col...
TableB
ID PK, FK to TableA.ID
Col1 unique index
Спасибо за комментарии, первоначальная версия этого ответа была неправильной.
Вот трюк с использованием вычисляемого столбца, который эффективно допускает ограничение уникальности, допускающее значение NULL, в SQL Server:
create table NullAndUnique
(
id int identity,
name varchar(50),
uniqueName as case
when name is null then cast(id as varchar(51))
else name + '_' end,
unique(uniqueName)
)
insert into NullAndUnique default values
insert into NullAndUnique default values -- Works
insert into NullAndUnique default values -- not accidentally :)
insert into NullAndUnique (name) values ('Joel')
insert into NullAndUnique (name) values ('Joel') -- Boom!
Он в основном использует id
, когда имя
имеет значение NULL. + '_'
предназначен для исключения случаев, когда имя может быть числовым, например 1
, которое может конфликтовать с id
.
Oracle знает. Полностью нулевой ключ не индексируется деревом B в индексе Oracle, и Oracle использует индексы дерева B для обеспечения уникальных ограничений.
Предполагая, что кто-то хочет версию ID_COLUMN на основе ACTIVE_FLAG, установленного на 1:
CREATE UNIQUE INDEX idx_versioning_id ON mytable
(CASE active_flag WHEN 0 THEN NULL ELSE active_flag END,
CASE active_flag WHEN 0 THEN NULL ELSE id_column END);