CODEWITHSUNDEEP
sqlsql-server
Fedor Hajdu
Fedor Hajdu

Reputation: 4697

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?

Thanks.

Upvotes: 3

Views: 281

Answers (7)

GarethD
GarethD

Reputation: 69759

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.

Upvotes: 0

Chris Gessler
Chris Gessler

Reputation: 23113

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

Upvotes: 0

anon
anon

Reputation:

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

Upvotes: 0

Jaimal Chohan
Jaimal Chohan

Reputation: 8645

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

Upvotes: 1

Lieven Keersmaekers
Lieven Keersmaekers

Reputation: 58431

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

Test script

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

Upvotes: 1

Rapha&#235;l Althaus
Rapha&#235;l Althaus

Reputation: 60493

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/

Untested example

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)

Upvotes: 3

Rahul
Rahul

Reputation: 77866

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

Upvotes: 0

Related Questions