Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Administration
 Unique constraint by groups

Author  Topic 

ITTrucker
Yak Posting Veteran

64 Posts

Posted - 2014-11-26 : 10:06:55
I have a xref table with three columns, RID, Category, Prime. I want to add a constraint to the table so for each RID, there is only a single 1 in the prime field but there can be multiple 0's. (I also want to only allow one Category per RID) The values below are all valid:

DECLARE @xPrime TABLE
( RID int,
Category int,
Prime bit )
INSERT INTO @xPrime (Value,Prime)
VALUES(1234,8,1),(1234,7,0),(1234,22,0),(4567,7,1),(9876,22,1),(9876,7,0)

I want to make sure I can't add another (1234,9,1) because there should only be one primary flag per ID. 1234,9,0 would be fine.

I also want to prevent anyone adding (1234,8,0) because there's already a Category value of 8 for that ID. 4567,8,0 would be fine.

Thanks

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-11-26 : 10:32:39
You'll need to write a function that takes in RID and Category and looks in the table for the presence of the combo (RID, Category, 1). The function should return a bit: 1 if found, 0 otherwise. Armed with that you can write your constraint:

Prime bit CONSTRAINT CK_CheckPrime CHECK (Prime = 0 or dbo.CheckPrime(RID, Category) = 0)
Go to Top of Page
   

- Advertisement -