Ограничение столбца бит SQL Server, 1 строка = 1, все остальные 0

Ответ 1

Все версии:

• Trigger
• Индексированное представление
• Сохраненный proc (например, тест при записи)

SQL Server 2008: отфильтрованный индекс

CREATE UNIQUE INDEX IX_foo ON bar (MyBitCol) WHERE MyBitCol = 1

Ответ 2

Предполагая, что ваш ПК - это один, числовой столбец, вы можете добавить вычисленный столбец в свою таблицу:

ALTER TABLE YourTable
  ADD IsDefaultCheck AS CASE IsDefault
     WHEN 1 THEN -1
     WHEN 0 THEN YourPK
  END

Затем создайте уникальный индекс в вычисленном столбце.

CREATE UNIQUE INDEX IX_DefaultCheck ON YourTable(IsDefaultCheck)

Ответ 3

Я думаю, что триггер - лучшая идея, если вы хотите изменить старую запись по умолчанию на 0, когда вы вставляете/обновляете новую, и если вы хотите, чтобы одна запись всегда имела это значение (т.е. если вы удаляете запись со значением, которое вы присвоили бы ему другой записи). Вам нужно будет принять решение о правилах для этого. Эти триггеры могут быть сложными, потому что вам приходится учитывать несколько записей во вставленных и удаленных таблицах. Итак, если 3 записи в пакете попытаются обновиться, чтобы стать записью по умолчанию, какой из них победит?

Если вы хотите убедиться, что одна запись по умолчанию никогда не изменяется, когда кто-то пытается ее изменить, отфильтрованный индекс - хорошая идея.

Ответ 4

Вы можете применить вместо триггера Insert и проверить значение при входе.

Create Trigger TRG_MyTrigger
on MyTable
Instead of Insert
as
Begin

  --Check to see if the row is marked as active....
  If Exists(Select * from inserted where IsDefault= 1)
  Begin
     Update Table Set IsDefault=0 where ID= (select ID from inserted);

     insert into Table(Columns)
     select Columns from inserted
  End

End

В качестве альтернативы вы можете применить уникальное ограничение для столбца.

Ответ 5

Принятый ответ на следующий вопрос интересен и уместен:

Ограничение только для одной записи, помеченной как по умолчанию

"Но серьезные реляционные люди расскажут вам эту информацию должен быть только в другой таблице."

У вас есть отдельная таблица из 1 строки, которая сообщает вам, какая запись является "по умолчанию". Анон затронул это в своем комментарии.

Я думаю, что это лучший подход - простой, чистый и не требует "умного" эзотерического решения, склонного к ошибкам или более позднему непониманию. Вы даже можете отбросить столбец IsDefualt.

Ответ 6

Здесь можно использовать различные подходы, но я думаю, что только два являются правильными. Но давайте сделаем это шаг за шагом.

У нас есть таблица Hierachy, в которой у нас есть столбец Root. В этом столбце указывается, какая строка является отправной точкой. Как и в вопросе, мы хотим иметь только одну отправную точку.

Мы считаем, что мы можем это сделать:

• скованность
• Индексированный просмотр
• Вызывать
• Различные таблицы и соотношение

скованность

В этом подходе сначала нам нужно создать функцию, которая будет выполнять эту работу.

CREATE FUNCTION [gt].[fnOnlyOneRoot]()
RETURNS BIT
BEGIN
    DECLARE @rootAmount TINYINT
    DECLARE @result BIT
    SELECT @rootAmount=COUNT(1) FROM [gt].[Hierarchy] WHERE [Root]=1

    IF @rootAmount=1 
        set @result=1
    ELSE 
        set @result=0
    RETURN @result
END
GO

И тогда ограничение:

ALTER TABLE [gt].[Hierarchy]  WITH CHECK ADD  CONSTRAINT [ckOnlyOneRoot] CHECK  (([gt].[fnOnlyOneRoot]()=(1)))

К сожалению подход ошибочен, поскольку это ограничение не позволит нам изменять любые значения в таблице. Он должен иметь ровно один отмеченный корень (вставка с Root = 1 будет генерировать исключение и обновление с помощью набора Root = 0 также)

Мы могли бы изменить fnOnyOneRoot, чтобы иметь 0 выбранных корней, но это не то, что мы хотели.

Индекс

Индекс удалит все строки, которые определены в предложении where, а остальные данные установят уникальное ограничение. Здесь у нас есть разные варианты: - Корень может быть нулевым, и мы можем добавить туда, где Root! = 0 и Root не является нулевым. Корень должен иметь значение, и мы можем добавить только здесь, где Root! = 0 - и разные комбинации

CREATE UNIQUE INDEX ix_OnyOneRoot ON [gt].[Hierarchy](Root) WHERE Root !=0 and Root is not null

Этот подход также не идеален. Максимум один корень будет вынужден, но минимальный нет. Чтобы обновить данные, нам нужно установить предыдущие строки равными нулю или 0.

Вызывать

Мы можем сделать два типа триггеров, которые ведут себя по-другому: предотвратите триггер - который не позволит нам помещать неправильные данные - триггер DoTheJob - который в фоновом режиме будет обновлять данные для нас

Предотвратить запуск

Это в основном то же самое, что ограничение, если мы хотим заставить только один корень, чем мы не можем обновить или вставить.

CREATE TRIGGER tOnlyOneRoot  
ON [gt].[Hierarchy]
AFTER INSERT, UPDATE   
AS
    DECLARE @rootAmount TINYINT
    DECLARE @result BIT
    SELECT @rootAmount=COUNT(1) FROM [gt].[Hierarchy] WHERE [Root]=1

    IF @rootAmount=1 
        set @result=1
    ELSE 
        set @result=0
    IF @result=0 
    BEGIN
    RAISERROR ('Only one root',0,0);  
    ROLLBACK TRANSACTION
    RETURN
    END
GO  

Триггер DoTheJob

Этот триггер будет проверять все вставленные/обновленные строки, и если будет передано более одного корня, он будет генерировать исключение. В другом случае, если новый Root будет обновлен или вставлен, триггер позволит это сделать, и после операции он изменит значение Root для всех остальных строк на 0.

CREATE TRIGGER tOnlyOneRootDoTheJob  
ON [gt].[Hierarchy]
AFTER INSERT, UPDATE   
AS
    DECLARE @insertedCount TINYINT

    SELECT @insertedCount = COUNT(1) FROM inserted WHERE [Root]=1
    if (@insertedCount  > 1)
    BEGIN
        RAISERROR ('Only one root',0,0);  
        ROLLBACK TRANSACTION
    RETURN
    END

    DECLARE @newRootId INT
    SELECT @newRootId = [HierarchyId] FROM inserted WHERE [Root]=1

    UPDATE [gt].[Hierarchy] SET [Root]=0 WHERE [HierarchyId] <> @newRootId

GO  

Это решение, которого мы пытались достичь. Только одно правило корня всегда встречается. (Дополнительный триггер для удаления должен быть выполнен)

Различные таблицы и соотношение

Это позволяет говорить более нормализованным образом. Мы создаем новую таблицу, чтобы иметь только одну строку (с использованием опций, описанных выше), и мы присоединяемся.

CREATE TABLE [gt].[HierarchyDefault](
    [HierarchyId] INT PRIMARY KEY NOT NULL,
    CONSTRAINT FK_HierarchyDefault_Hierarchy FOREIGN KEY (HierarchyId) REFERENCES [gt].[Hierarchy](HierarchyId)
    )

Это повлияет на производительность?

С одной колонкой

SET STATISTICS TIME ON; 
    SELECT [HierarchyId],[ParentHierarchyId],[Root]
    FROM [gt].[Hierarchy] WHERE [root]=1
SET STATISTICS TIME OFF; 

Результат CPU time = 0 мс, прошедшее время = 0 мс.

С присоединением:

SET STATISTICS TIME ON; 
    SELECT h.[HierarchyId],[ParentHierarchyId],[Root]
    FROM [gt].[Hierarchy] h
    INNER JOIN [gt].[HierarchyDefault] hd on h.[HierarchyId]=hd.[HierarchyId]
    WHERE [root]=1
 SET STATISTICS TIME OFF; 

Результат CPU time = 0 мс, прошедшее время = 0 мс.

Резюме Я буду использовать триггер. Это какая-то магия в таблице, но она все работала под капотом.

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

CREATE TABLE [gt].[Hierarchy](
    [HierarchyId] INT PRIMARY KEY IDENTITY(1,1),
    [ParentHierarchyId] INT NULL,
    [Root] BIT
    CONSTRAINT FK_Hierarchy_Hierarchy FOREIGN KEY (ParentHierarchyId) 
 REFERENCES [gt].[Hierarchy](HierarchyId)
)