Single default value in a table
I'm creating a table that'll have a single bit not null column IsDefault. I need to write a constraint that'll make sure there'll be only one default value per UserId (field in the same table). I can't use unique constraint on this because it is possible to have many non-default values.
What is the best approach to do this using MS SQL Server 2008?
The easiest way I see is a check constraint with a UDF (User Defined function).
Look at here, for example. http://sqljourney.wordpress.com/2010/06/25/check-constraint-with-user-defined-function-in-sql-server/
CREATE FUNCTION dbo.CheckDefaultUnicity(@UserId int) RETURNS int AS BEGIN DECLARE @retval int SELECT @retval = COUNT(*) FROM <your table> where UserId = @UserId and <columnwithDefault> = 1-- or whatever is your default value RETURN @retval END; GO
and alter your table
ALTER TABLE <yourTable> ADD CONSTRAINT Ck_UniqueDefaultForUser CHECK (dbo.CheckDefaultUnicity(UserId) <2)
Another relatively simple option is to use a CLUSTERED INDEXED VIEW. The gist of this is to
- Select all UserID's from your table where IsDefault=1 in a view.
- Add a unique index on UserID
Clustered indexed view
CREATE VIEW dbo.VIEW_Users_IsDefault WITH SCHEMABINDING AS SELECT UserID, IsDefault FROM dbo.Users WHERE IsDefault = 1 GO CREATE UNIQUE CLUSTERED INDEX UIX_VIEW_USERS_ISDEFAULT ON dbo.VIEW_Users_IsDefault (UserID) GO
BEGIN TRAN CREATE TABLE dbo.Users (UserID INT, IsDefault BIT) GO CREATE VIEW dbo.VIEW_Users_IsDefault WITH SCHEMABINDING AS SELECT UserID, IsDefault FROM dbo.Users WHERE IsDefault = 1 GO CREATE UNIQUE CLUSTERED INDEX UIX_VIEW_USERS_ISDEFAULT ON dbo.VIEW_Users_IsDefault (UserID) GO INSERT INTO dbo.Users VALUES (1, 0) INSERT INTO dbo.Users VALUES (1, 1) INSERT INTO dbo.Users VALUES (1, 1) -- Fails because of clustered index ROLLBACK TRAN
A Check Constraint would definitely work, however it's not good a design choice in my opinion. The reason being that your UDF for the constraint would be something like
SELECT @Count = COUNT(UserId) FROM User WHERE IsDefault = 1 GROUP BY UserId HAVING COUNT(UserId) > 1 IF @Count > 0 ....'FAIL
As this touches 2 columns it would thus need to be a Table level constraint and the more records you have the slower a Insert/Update/Delete will take.
A better option would be to only allow access to that table via Stored Procedure, so before an insert/update you could run a very quick
IF EXISTS(SELECT UserId FROM User where UserId = @UserId and IsDefault = 1)
before your inserts/updates/deletes
I can however appreciate that you may be using an ORM and might not want to have Stored Procs in your system so you could change the design of your table to the below. This assumes that
tblUser: UserId, FirstName, Suraname, etc
tblUserDefault: UserId (Unique Constraint)
I'm not sure what IsDefault represents in your system so I'm assuming in the above that Users are either default or not. Anybody you can use that as a reference. It allows you to enforce the constraint without using USP's or horrid tablewide check constraints (or triggers) and would be mappable in any decent ORM
What about CHECK Constraints. See here: http://msdn.microsoft.com/en-us/library/ms188258(v=sql.105).aspx
ALTER TABLE yourtable ADD CONSTRAINT IsDefaultChecked CHECK (IsDefault = T );
While I think the trigger and constraint solutions are better, if you control insert/update via stored procedure a much simpler approach would be to just update the conflicting rows first (assuming the new default always wins):
ALTER PROCEDURE dbo.UserWhateverTable_<action> @UserID INT, @CardID INT AS BEGIN SET NOCOUNT ON; UPDATE dbo.UserWhatever SET IsDefault = 0 WHERE UserID = @UserID AND CardID = @CardID AND IsDefault = 1; -- insert or update here END GO
In fact it might not be a bad idea to do this (so the business logic is 100% clear in your DML procedures) in addition to guarding it with a trigger or a constraint (to catch cases where updates are made outside of your procedures).
You might want to use a trigger in this case. When the user is changing their default, the trigger could automatically flip the current default for the current user to false.
Basically, use an AFTER insert/update trigger to set the IsDefault column to 0 for any user in the insert/update where the IsDefault value is being set to 1.
CREATE TRIGGER dbo.tr_default ON dbo.MyTable AFTER INSERT, UPDATE AS if(exists(select * from inserted where IsDefault = 1) begin update dbo.MyTable set IsDefault = 0 from inserted i join dbo.MyTable t on i.userid = t.userid where i.IsDefault = 1 and i.TheValue != t.TheValue end
I don't see any problem with any of the answers so far suggesting CHECK CONSTRAINTS and TRIGGERS however, it seems like a bit of a backwards solution to me.
I can only assume UserID in your table is a foreign key to a User table, so why not add a column to your User table to store the default CardID, rather than marking one as default? This makes it impossible for a user to have multiple default CardIDs without costly triggers/constraints. If you make the column non nullable then it is also impossible for a user not to have a default CardID if you so wish.