November 23, 2005 at 8:47 am
Is it possible to have a constraint that checks another field.
The idea is two mutually exclusive fields.If one field has a value other than null the other field has to be null.
Alternatively there has to be a value in one and only one of the two fields.
Thanks in advance for your time and imput
Mike
November 23, 2005 at 9:43 am
Seems strange to request that, what is the business rule you have to enforce?
November 23, 2005 at 10:15 am
Since you asked ...
Software should either be handed out to a person or installed in some hardware but not both.
This is how it has been implemented Constraint
Asset is not null and Employeeid is null or asset is null and employeeid is not null seems to work.
Will be tested to confirm the functionality
Mike
November 23, 2005 at 11:41 am
Maybe I'm wrong but it looks like you could have a delivery type column with a lookup table. If you make that column not null then 1 value can be selected but only one. Also if you ever need other possibilities, then the program still works.
November 23, 2005 at 12:43 pm
I agree with RGR'us. It sounds like you would be better served using one column to make this work. Otherwise, you would have to program this into the table triggers.
November 23, 2005 at 4:15 pm
Not quite, a single constraint could validate this requirement but I feel that my suggestion allows for more possibilities if the rules (gob forbid) ever change. The overhead would be about the same to have either a constraint (check or FK).
The check constraint would look something like this :
(Col1 is null and Col2 is not null) OR (Col1 is not null and Col2 is null).
The only advantage of the trigger in this situation would be that you could return a user defined error message instead of the default constraint errors of the server.
November 28, 2005 at 9:10 am
There is a completely naughty way to do that.
Create table WTF (
c1 varchar(10) NULL,
c2 varchar(10) NULL)
GO
Alter table WTF
add FOO as 1/((CASE when c1 IS NULL then 1 else 0 end
+ CASE when c2 IS NULL then 1 else 0 end)
% 2)
GO
Create Index BAR on WTF(FOO)
GO
If you violate the "one and only one not null" rule, your insert will fail due to a divide by zero error.
/Pure evil
November 28, 2005 at 2:42 pm
I seriously hope you're kidding with that one???
November 28, 2005 at 7:30 pm
No constraint. No trigger. Why not?
Okay, I was kidding.
November 28, 2005 at 9:36 pm
How is that informative to the users, the developpers? How do you track that error without documentation??
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply