March 26, 2009 at 6:09 am
Hi,
I have a table with amongst others, 3 columns of type int and 1 column of type datetime. The 3 int columns (e.g. col1, col2 and col3) accept null values whereas the datetime col (col4) doesn't. I'd like to enforce some business rules at the table level using indexes or constraints (or by any other means suggested here, e.g. triggers?) but I'm not quite which is best to implement given my situation.
Rule 1 would be that each row must have a non null value in either col2 or col3 but not in both. Having a non null value in col1 is optional.
Rule 2 would be that that the combination of col1, col2, col3 and col4 would be unique (e.g. no two rows can have the same combination of values). It's important that null values are taken into account (i.e. that null value are not ignored).
These rules must be enforced when inserting new row and updating existing rows (and I can't redesign the table structure itself)
Thanks,
Norman
March 26, 2009 at 6:15 am
Use a check constraint on the table for the first rule:
( case when col2 is not null then 1 else 0 end + case when col3 is not null then 1 else 0 end ) = 1
Use a unique constraint on the table over the four columns for the second rule.
March 26, 2009 at 7:02 am
Very nice - thanks.
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply