Как создать уникальное ограничение, которое также допускает нулевые значения?
Я хочу иметь уникальное ограничение для столбца, который я собираюсь заполнить GUID. Тем не менее, мои данные содержат нулевые значения для этих столбцов. Как создать ограничение, которое допускает множественные нулевые значения?
Вот пример сценария. Рассмотрим эту схему:
CREATE TABLE People (
Id INT CONSTRAINT PK_MyTable PRIMARY KEY IDENTITY,
Name NVARCHAR(250) NOT NULL,
LibraryCardId UNIQUEIDENTIFIER NULL,
CONSTRAINT UQ_People_LibraryCardId UNIQUE (LibraryCardId)
)
Тогда посмотрите этот код для того, что я пытаюсь достичь:
-- This works fine:
INSERT INTO People (Name, LibraryCardId)
VALUES ('John Doe', 'AAAAAAAA-AAAA-AAAA-AAAA-AAAAAAAAAAAA');
-- This also works fine, obviously:
INSERT INTO People (Name, LibraryCardId)
VALUES ('Marie Doe', 'BBBBBBBB-BBBB-BBBB-BBBB-BBBBBBBBBBBB');
-- This would *correctly* fail:
--INSERT INTO People (Name, LibraryCardId)
--VALUES ('John Doe the Second', 'AAAAAAAA-AAAA-AAAA-AAAA-AAAAAAAAAAAA');
-- This works fine this one first time:
INSERT INTO People (Name, LibraryCardId)
VALUES ('Richard Roe', NULL);
-- THE PROBLEM: This fails even though I'd like to be able to do this:
INSERT INTO People (Name, LibraryCardId)
VALUES ('Marcus Roe', NULL);
Окончательное утверждение завершается ошибкой с сообщением:
Нарушение ограничения UNIQUE KEY 'UQ_People_LibraryCardId'. Невозможно вставить дубликат ключа в объект 'dbo.People'.
Как я могу изменить мою схему и / или ограничение уникальности, чтобы оно NULL значения, при этом все еще проверяя уникальность на реальных данных?
15 ответов
SQL Server 2008 +
Вы можете создать уникальный индекс, который принимает несколько NULL с WHERE пункт. Смотрите ответ ниже.
До SQL Server 2008
Вы не можете создать УНИКАЛЬНОЕ ограничение и разрешить NULL. Вам нужно установить значение по умолчанию NEWID().
Обновите существующие значения до NEWID (), где NULL, перед созданием ограничения UNIQUE.
То, что вы ищете, действительно является частью стандартов ANSI SQL:92, SQL:1999 и SQL:2003, то есть ограничение UNIQUE должно запрещать повторяющиеся значения, отличные от NULL, но принимать несколько значений NULL.
Однако в мире Microsoft SQL Server допускается использование одного NULL, а нескольких NULL - нет...
В SQL Server 2008 вы можете определить уникальный отфильтрованный индекс на основе предиката, который исключает NULL:
CREATE UNIQUE NONCLUSTERED INDEX idx_yourcolumn_notnull
ON YourTable(yourcolumn)
WHERE yourcolumn IS NOT NULL;
В более ранних версиях вы можете прибегнуть к VIEWS с предикатом NOT NULL, чтобы применить ограничение.
SQL Server 2008 и выше
Просто отфильтруйте уникальный индекс:
CREATE UNIQUE NONCLUSTERED INDEX UQ_Party_SamAccountName
ON dbo.Party(SamAccountName)
WHERE SamAccountName IS NOT NULL;
В более низких версиях материализованное представление все еще не требуется
Для SQL Server 2005 и более ранних версий вы можете сделать это без представления. Я только что добавил уникальное ограничение, как вы просите, к одной из моих таблиц. Учитывая, что я хочу уникальности в колонке SamAccountName, но я хочу разрешить несколько значений NULL, я использовал материализованный столбец, а не материализованное представление:
ALTER TABLE dbo.Party ADD SamAccountNameUnique
AS (Coalesce(SamAccountName, Convert(varchar(11), PartyID)))
ALTER TABLE dbo.Party ADD CONSTRAINT UQ_Party_SamAccountName
UNIQUE (SamAccountNameUnique)
Вы просто должны поместить что-то в вычисляемый столбец, который будет гарантированно уникальным по всей таблице, когда фактический желаемый уникальный столбец равен NULL. В этом случае, PartyID является столбцом идентификатора, и числовое значение никогда не будет соответствовать SamAccountNameтак у меня получилось. Вы можете попробовать свой собственный метод - убедитесь, что вы понимаете предметную область ваших данных, чтобы не было возможности пересечения с реальными данными. Это может быть так же просто, как добавить символ дифференцирования, например так:
Coalesce('n' + SamAccountName, 'p' + Convert(varchar(11), PartyID))
Даже если PartyID когда-нибудь стал нечисловым и мог совпадать с SamAccountNameТеперь это не имеет значения.
Обратите внимание, что наличие индекса, включающего вычисляемый столбец, неявно приводит к тому, что каждый результат выражения сохраняется на диск вместе с другими данными в таблице, что ДОЛЖНО занимать дополнительное дисковое пространство.
Обратите внимание, что если вы не хотите индексировать, вы все равно можете сохранить ЦП, предварительно рассчитав выражение на диске, добавив ключевое слово PERSISTED до конца определения выражения столбца.
В SQL Server 2008 и выше обязательно используйте фильтрованное решение, если это возможно!
полемика
Обратите внимание, что некоторые специалисты по базам данных будут рассматривать это как случай "суррогатных NULL", которые, безусловно, имеют проблемы (в основном из-за проблем, связанных с попыткой определить, является ли что-то реальным или суррогатным значением для отсутствующих данных; также могут быть проблемы). с числом ненулевых суррогатных значений, умножающихся как сумасшедшие).
Тем не менее, я считаю, что этот случай отличается. Вычисляемый мной столбец никогда не будет использоваться для определения чего-либо. Он не имеет смысла сам по себе и не кодирует информацию, которая не найдена отдельно в других, правильно определенных столбцах. Это никогда не должно быть выбрано или использовано.
Итак, моя история такова, что это не суррогатный NULL, и я придерживаюсь его! Поскольку на самом деле мы не хотим, чтобы значение, отличное от NULL, использовалось не для того, чтобы обмануть UNIQUE Индекс для игнорирования NULL, наш вариант использования не имеет проблем, возникающих при обычном создании суррогатного NULL.
Несмотря на все сказанное, у меня нет проблем с использованием индексированного представления вместо этого, но это вызывает некоторые проблемы, такие как требование использования SCHEMABINDING, Получайте удовольствие, добавляя новый столбец в вашу базовую таблицу (вам как минимум придется отбросить индекс, а затем отбросить представление или изменить представление, чтобы оно не было привязано к схеме). См. Полный (длинный) список требований для создания индексированного представления в SQL Server (2005) (также более поздние версии), (2000).
Обновить
Если ваш столбец числовой, может возникнуть проблема с обеспечением уникального ограничения с использованием Coalesce не приводит к столкновениям. В этом случае есть несколько вариантов. Можно было бы использовать отрицательное число, чтобы поместить "суррогатные значения NULL" только в отрицательный диапазон, а "реальные значения" только в положительный диапазон. В качестве альтернативы можно использовать следующий шаблон. В таблице Issue (где IssueID это PRIMARY KEY), может быть или не быть TicketID, но если он есть, он должен быть уникальным.
ALTER TABLE dbo.Issue ADD TicketUnique
AS (CASE WHEN TicketID IS NULL THEN IssueID END);
ALTER TABLE dbo.Issue ADD CONSTRAINT UQ_Issue_Ticket_AllowNull
UNIQUE (TicketID, TicketUnique);
Если IssueID 1 имеет билет 123, UNIQUE ограничение будет на значения (123, NULL). Если IssueID 2 не имеет билета, он будет включен (NULL, 2). Некоторые рассуждения покажут, что это ограничение не может быть продублировано для какой-либо строки в таблице, и, тем не менее, допускает наличие нескольких пустых значений.
Для людей, которые используют Microsoft SQL Server Manager и хотят создать уникальный, но обнуляемый индекс, вы можете создать свой уникальный индекс, как обычно, затем в свойствах индекса для нового индекса выберите "Фильтр" на левой панели и введите ваш фильтр (который является вашим предложением where). Следует читать что-то вроде этого:
([YourColumnName] IS NOT NULL)
Это работает с MSSQL 2012
Это можно сделать и в дизайнере
Щелкните правой кнопкой мыши на Index> Properties, чтобы открыть это окно.
Когда я применил уникальный индекс ниже:
CREATE UNIQUE NONCLUSTERED INDEX idx_badgeid_notnull
ON employee(badgeid)
WHERE badgeid IS NOT NULL;
каждое ненулевое обновление и вставка не выполнялись с ошибкой ниже:
ОБНОВЛЕНИЕ не удалось, потому что следующие параметры SET имеют неправильные настройки: "ARITHABORT".
Я нашел это на MSDN
SET ARITHABORT должен быть включен, когда вы создаете или изменяете индексы для вычисляемых столбцов или индексированных представлений. Если SET ARITHABORT имеет значение OFF, операторы CREATE, UPDATE, INSERT и DELETE для таблиц с индексами для вычисляемых столбцов или индексированных представлений завершатся неудачно.
Чтобы заставить это работать правильно, я сделал это
Щелкните правой кнопкой мыши [База данных]-> Свойства -> Параметры -> Другие параметры -> Разное -> Арифметическое прерывание включено ->true
Я считаю, что можно установить эту опцию в коде, используя
ALTER DATABASE "DBNAME" SET ARITHABORT ON
но я не проверял это
Можно создать уникальное ограничение для кластеризованного индексированного представления
Вы можете создать вид следующим образом:
CREATE VIEW dbo.VIEW_OfYourTable WITH SCHEMABINDING AS
SELECT YourUniqueColumnWithNullValues FROM dbo.YourTable
WHERE YourUniqueColumnWithNullValues IS NOT NULL;
и уникальное ограничение, подобное этому:
CREATE UNIQUE CLUSTERED INDEX UIX_VIEW_OFYOURTABLE
ON dbo.VIEW_OfYourTable(YourUniqueColumnWithNullValues)
Создайте представление, которое выбирает толькоNULL столбцы и создать UNIQUE INDEX на вид:
CREATE VIEW myview
AS
SELECT *
FROM mytable
WHERE mycolumn IS NOT NULL
CREATE UNIQUE INDEX ux_myview_mycolumn ON myview (mycolumn)
Обратите внимание, что вам нужно выполнить INSERTи UPDATEна виду вместо таблицы.
Вы можете сделать это с INSTEAD OF спусковой крючок:
CREATE TRIGGER trg_mytable_insert ON mytable
INSTEAD OF INSERT
AS
BEGIN
INSERT
INTO myview
SELECT *
FROM inserted
END
По моему опыту - если вы думаете, что столбец должен допускать значения NULL, но также должен быть УНИКАЛЬНЫМ для значений, где они существуют, возможно, вы неправильно моделируете данные. Это часто предполагает, что вы создаете отдельную подобъект в той же таблице в качестве другого объекта. Вероятно, имеет смысл поместить эту сущность во вторую таблицу.
В приведенном примере я бы поместил LibraryCardId в отдельную таблицу LibraryCards с уникальным ненулевым внешним ключом для таблицы People:
CREATE TABLE People (
Id INT CONSTRAINT PK_MyTable PRIMARY KEY IDENTITY,
Name NVARCHAR(250) NOT NULL,
)
CREATE TABLE LibraryCards (
LibraryCardId UNIQUEIDENTIFIER CONSTRAINT PK_LibraryCards PRIMARY KEY,
PersonId INT NOT NULL
CONSTRAINT UQ_LibraryCardId_PersonId UNIQUE (PersonId),
FOREIGN KEY (PersonId) REFERENCES People(id)
)
Таким образом, вам не нужно беспокоиться о том, что столбец является одновременно уникальным и допускающим значение NULL. Если у человека нет читательского билета, у него просто не будет записи в таблице читательского билета. Кроме того, если в библиотечной карточке есть дополнительные атрибуты (например, дата истечения срока или что-то в этом роде), теперь у вас есть логическое место для размещения этих полей.
Как указано выше, SQL Server не реализует стандарт ANSI, когда дело доходит до UNIQUE CONSTRAINT, На Microsoft Connect есть билет с 2007 года. Как уже было предложено, здесь и сейчас лучшие варианты на сегодняшний день - использовать отфильтрованный индекс, как указано в другом ответе или вычисляемом столбце, например:
CREATE TABLE [Orders] (
[OrderId] INT IDENTITY(1,1) NOT NULL,
[TrackingId] varchar(11) NULL,
...
[ComputedUniqueTrackingId] AS (
CASE WHEN [TrackingId] IS NULL
THEN '#' + cast([OrderId] as varchar(12))
ELSE [TrackingId_Unique] END
),
CONSTRAINT [UQ_TrackingId] UNIQUE ([ComputedUniqueTrackingId])
)
Может быть, рассмотримINSTEAD OF"вызвать и выполнить проверку самостоятельно? С некластеризованным (неуникальным) индексом для столбца, чтобы включить поиск.
Вы можете создать триггер INSTEAD OF для проверки определенных условий и ошибок, если они выполняются. Создание индекса может быть дорогостоящим для больших таблиц.
Вот пример:
CREATE TRIGGER PONY.trg_pony_unique_name ON PONY.tbl_pony
INSTEAD OF INSERT, UPDATE
AS
BEGIN
IF EXISTS(
SELECT TOP (1) 1
FROM inserted i
GROUP BY i.pony_name
HAVING COUNT(1) > 1
)
OR EXISTS(
SELECT TOP (1) 1
FROM PONY.tbl_pony t
INNER JOIN inserted i
ON i.pony_name = t.pony_name
)
THROW 911911, 'A pony must have a name as unique as s/he is. --PAS', 16;
ELSE
INSERT INTO PONY.tbl_pony (pony_name, stable_id, pet_human_id)
SELECT pony_name, stable_id, pet_human_id
FROM inserted
END
CREATE UNIQUE NONCLUSTERED INDEX [UIX_COLUMN_NAME]
ON [dbo].[Employee]([Username] ASC) WHERE ([Username] IS NOT NULL)
WITH (ALLOW_PAGE_LOCKS = ON, ALLOW_ROW_LOCKS = ON, PAD_INDEX = OFF, SORT_IN_TEMPDB = OFF,
DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, STATISTICS_NORECOMPUTE = OFF, ONLINE = OFF,
MAXDOP = 0) ON [PRIMARY];
Вы не можете сделать это с UNIQUE ограничение, но вы можете сделать это в триггере.
CREATE TRIGGER [dbo].[OnInsertMyTableTrigger]
ON [dbo].[MyTable]
INSTEAD OF INSERT
AS
BEGIN
SET NOCOUNT ON;
DECLARE @Column1 INT;
DECLARE @Column2 INT; -- allow nulls on this column
SELECT @Column1=Column1, @Column2=Column2 FROM inserted;
-- Check if an existing record already exists, if not allow the insert.
IF NOT EXISTS(SELECT * FROM dbo.MyTable WHERE Column1=@Column1 AND Column2=@Column2 @Column2 IS NOT NULL)
BEGIN
INSERT INTO dbo.MyTable (Column1, Column2)
SELECT @Column2, @Column2;
END
ELSE
BEGIN
RAISERROR('The unique constraint applies on Column1 %d, AND Column2 %d, unless Column2 is NULL.', 16, 1, @Column1, @Column2);
ROLLBACK TRANSACTION;
END
END
Этот код, если вы делаете регистрационную форму с textBox и используете вставку, а ваш textBox пуст и нажимаете кнопку отправки.
CREATE UNIQUE NONCLUSTERED INDEX [IX_tableName_Column]
ON [dbo].[tableName]([columnName] ASC) WHERE [columnName] !=`''`;