Check constraint with only one value SQL Server 2005

Question

I have a table with these fields:

User_id, User_type, User_address

Is it possible to add a constraint where only one record can exist where user_type = 'xyz' per user_id? There can be as many user_type = 'abc' as we wish but only one 'xyz'.

I know that this is not the greatest design but it is what is there currently and I need to lock it down a bit.

Thanks

Answer 1

you'll need to use a trigger...

CREATE TRIGGER yourTriggerName ON YourTableName
AFTER INSERT,UPDATE
AS

IF EXISTS (SELECT
               y.User_id --,COUNT(y.User_Type)
               FROM YourTableName       y 
                   INNER JOIN inserted  i ON y.User_id=i.User_id
               WHERE y.User_Type='xyz'
               GROUP BY y.User_id 
               HAVING COUNT(y.User_Type)>1
          )
BEGIN

    ROLLBACK

END
go

also, make sure there is an index on User_id+User_type

Answer 2

I'm not sure its the best way but you could always create a insert/update trigger

Answer 3

You can use a check constraint to enforce this rule.

CREATE FUNCTION CheckUserTypeXyzExistAtMostOnce()
RETURNS bit
AS 
BEGIN
   DECLARE @count int
   SELECT @count = COUNT(*) FROM dbo.MyTable WHERE UserType = 'xyz'
   RETURN @count <= 1
END;

ALTER TABLE dbo.MyTable
ADD CONSTRAINT UserTypeConstraint CHECK (dbo.CheckUserTypeXyzExistAtMostOnce());

Answer 4

I had the same idea as Daniel, but I think your constraint as you put it needs to check for at most 1 XYZ type PER USER:

CREATE FUNCTION CheckUserTypeXyzExistAtMostOnce(@User_id int)
RETURNS bit
AS
BEGIN
    DECLARE @count int
    SELECT @count = COUNT(*) FROM dbo.MyTable WHERE User_id = @User_id AND User_type = 'xyz'
    RETURN @count <= 1
END;
ALTER TABLE dbo.MyTableADD CONSTRAINT UserTypeConstraint CHECK (dbo.CheckUserTypeXyzExistAtMostOnce(User_id));

Answer 5

A very common question. My canned answer:

Use Computed Columns to Implement Complex Business Rules

You can also use an indexed view to accomplish the same. Note that wrapping a UDF in a check constraint may not work if you modify more than one row at a time or if you use snapshot isolation:

Scalar UDFs wrapped in CHECK constraints are very slow and may fail for multirow updates

Why am I recommending an index, not a trigger? Because if I have an index I am 100% sure all my data is clean. With triggers, it is not the case. Sometimes triggers do not fire, sometimes they have bugs. Another trigger can override this one.