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.
| 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) |
 |
|
|
|
|
|
|
|
Unique constraint by groups