March 12, 2009 at 2:23 pm
I need to create a check constraint on a phone table.
This table has :
phone_id (PK, identity),
user_id (FK),
phone_num,
decsription,
is_primary bit
The rule is that only one phone for a given user can be primary.
In a check constraint design window I tried to implement something like this:
select user_id, is_primary, count(*)
from phones
where is_primary = 1
group by user_id, is_primary
having count(*)<=1
but get an error when trying to save it. B.O.L. does not have examples for such case.
Is it possible to enforce it with check constraint?
Thanks
March 12, 2009 at 2:31 pm
SQL Guy (3/12/2009)
I need to create a check constraint on a phone table.This table has :
phone_id (PK, identity),
user_id (FK),
phone_num,
decsription,
is_primary bit
The rule is that only one phone for a given user can be primary.
In a check constraint design window I tried to implement something like this:
select user_id, is_primary, count(*)
from phones
where is_primary = 1
group by user_id, is_primary
having count(*)<=1
but get an error when trying to save it. B.O.L. does not have examples for such case.
Is it possible to enforce it with check constraint?
Thanks
You may want to read CREATE TABLE in Books Online. What you are looking for, probably, is to implement a table leve CHECK constraint.
March 12, 2009 at 2:48 pm
You can use a function to do you check constraint ...
Ref: http://msdn.microsoft.com/en-us/library/ms188258.aspx
Mohit K. Gupta, MCITP: Database Administrator (2005), My Blog, Twitter: @SQLCAN[/url].
Microsoft FTE - SQL Server PFE
* Some time its the search that counts, not the finding...
* I didn't think so, but if I was wrong, I was wrong. I'd rather do something, and make a mistake than be frightened and be doing nothing. :smooooth:[/font]
March 12, 2009 at 3:06 pm
Mohit (3/12/2009)
Ref: http://msdn.microsoft.com/en-us/library/ms188258.aspx%5B/quote%5D
It still needs to be a TABLE level CHECK contraint.
March 12, 2009 at 3:22 pm
I checked BOL, but they don't have examples for it. Only logical_expression.
March 12, 2009 at 3:34 pm
I wasn't suggesting it wasn't sorry if it sounded that way ...
Mohit.
Mohit K. Gupta, MCITP: Database Administrator (2005), My Blog, Twitter: @SQLCAN[/url].
Microsoft FTE - SQL Server PFE
* Some time its the search that counts, not the finding...
* I didn't think so, but if I was wrong, I was wrong. I'd rather do something, and make a mistake than be frightened and be doing nothing. :smooooth:[/font]
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply