SQL Server bit column constraint, 1 row = 1, all others 0_问答_开发者

I have a bit IsDefault column. Only one row of data within the table开发者_运维问答 may have this bit column set to 1, all the others must be 0.

How can I enforce this?

All versions:

Trigger
Indexed view
Stored proc (eg test on write)

SQL Server 2008: a filtered index

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

Assuming your PK is a single, numeric column, you could add a computed column to your table:

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

Then create a unique index on the computed column.

CREATE UNIQUE INDEX IX_DefaultCheck ON YourTable(IsDefaultCheck)

I think the trigger is the best idea if you want to change the old default record to 0 when you insert/update a new one and if you want to make sure one record always has that value (i.e. if you delete the record with the value you would assign it to a different record). You would have to decide on the rules for doing so. These triggers can be tricky because you have to account for multiple records in the inserted and deleted tables. So if 3 records in a batch try to update to become the default record, which one wins?

If you want to make sure the one default record never changes when someone else tries to change it, the filtered index is a good idea.

Different approaches can be taken here, but I think only two are correct. But lets do it step by step.

We have table Hierachy table in which we have Root column. This column tells us what row is currently the starting point. As in question asked, we want to have only one starting point.

SQL Server bit column constraint, 1 row = 1, all others 0

We think that we can do it with:

Constraint
Indexed View
Trigger
Different table and relation

Constraint

In this approach first we need to create function which will do the job.

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

And then the constraint:

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

Unfortunately approach is wrong as this constraint won't allow us to change any values in the table. It need to have exactly one root marked (insert with Root=1 will throw exception, and update with set Root=0 also)

We could change the fnOnyOneRoot to allow having 0 selected roots but it not what we wanted.

Index

Index will remove all rows which are defined in the where clause and on the rest data will setup unique constraint. We have different options here: - Root can be nullable and we can add in where Root!=0 and Root is not null - Root must have value and we can add only in where Root!=0 - and different combinations

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

This approach also is not perfect. Maximum one Root will be forced, but minimum not. To update data we need to set previous rows to null or 0.

Trigger

We can do two kinds of trigger both behaves differently - Prevent trigger - which won't allow us to put wrong data - DoTheJob trigger - which in background will update data for us

Prevent trigger

This is basically the same as constraint, if we want to force only one root than we cannot update or insert.

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 trigger

This trigger will check for all inserted/updated rows and if more than one Root will be passed it will throw exception. In other case, so if one new Root will be updated or inserted, trigger will allow to do it and after operation it will change Root value for all other rows to 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

This is the solution we tried to achieve. Only one root rule is always meet. (Additional trigger for Delete should be done)

Different table and relation

This is lets say more normalized way. We create new table allow only to have one row (using the options described above) and we join.

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

Does it will hit the performance?

With one column

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

Result CPU time = 0 ms, elapsed time = 0 ms.

With join:

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;

Result CPU time = 0 ms, elapsed time = 0 ms.

Summary I will use the trigger. It is some magic in the table, but it did all job under the hood.

Easy table creation:

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)
)

You could apply an Instead of Insert trigger and check the value as it's coming in.

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

Alternatively you could apply a unique constraint on the column.

The accepted answer to the below question is both interesting and relevant:

Constraint for only one record marked as default

"But the serious relational folks will tell you this information should just be in another table."

Have a separate 1 row table that tells you which record is 'default'. Anon touched on this in his comment.

I think this is the best approach - simple, clean & doesn't require a 'clever' esoteric solution prone to errors or later misunderstanding. You can even drop the IsDefualt column.