Check limit to only one value in SQL Server 2005
I have a table with these fields:
User_id, User_type, User_address
Is it possible to add a constraint where only one entry can exist where user_type = 'xyz' for user_id? There can be as many user_type = 'abc as we want, but only one xyz.
I know this is not the best design, but this is what it currently is and I need to block it a bit.
thank
source to share
you 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 a pointer to User_id + User_type
source to share
A very common question. My canned answer:
Use calculated columns to enforce complex business rules
You can also use Indexed View to achieve the same. Note that wrapping a UDF in a check constraint may not work if you are modifying multiple lines at a time or using snapshot isolation:
Scalar UDFs wrapped in CHECK constraints are very slow and can fail for multiple updates
Why do I recommend an index and not a trigger? Because if I have an index, I am 100% sure that all my data is clean. This is not the case with triggers. Sometimes triggers don't fire, sometimes they have bugs. Another trigger can override this one.
source to share
I had the same idea as Daniel, but I think your constraint, as you put it, should check at most 1 XYZ PER USER type:
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));
source to share
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());
source to share