August 13, 2007 at 9:45 am
I'm a relative newcomer to SQL server and am having some difficulty with adding a check constraint on one of my tables.
The first part works;
alter table tblscreening
add constraint chkLabID
check
(LabID between 1 and 35)
But what I really need to say is (LabID between 1 and 35 or is null). How can I do this?
I'm also puzzled about the 'default value or binding' property of a column. Suppose that for 'LabID' I've set its default value to (NULL). Which takes precedance when SQL Server validates data on an insert/update, the check constraint or the default value?
I hope I've made myself clear and thanks for any help.
Mo
August 13, 2007 at 9:50 am
LabID between 1 and 35 or LabID is null
Don't use the default and you'll be fine.
As for precedence I never really thaught about this one. I think I would code it like this if I were MS : Check for null, if value is not specified and null is forbiden then check for default value and set it, then check for constraint check / foreign and primary keys, then unique index then triggers. Send change to log and commit.
This is only my guess, without any proof or test to back it up.
August 13, 2007 at 9:55 am
Thanks very much for the reply. I'll give it a shot.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply