Creating a constraint

Posted on

Question :

I created a report that records entries
from a table which has 4 columns

1 Shipper ID 
2 SHipper Reference
3 loading
4 Completion

My primary keys are columns 1 and 2 there are no foriegn keys, columns 3 and 4 are mandatory fileds
with a yes or no value constraint

what I want is to create a constraint that doesnt allow the user to pick these two fields (completeion and loading) as N in the same time but they can be both Y at the same time

what is the code for that constraint?

this code was my best shot

ADD CONSTRAINT Double_constraint check (Completion!='N' and Loading!='N')

but this one wont allow anything but completion to be Y and Loading to be N

Answer :

You’d probably be better off making this a bit field with NOT NULL or otherwise you also need to validate that only the strings 'Y' and 'N' were entered.

With the bit column arrangement the check constraint would be

CHECK (1 IN (Completion, Loading))

Otherwise with char(1) the check constraint could be

CHECK ('Y' IN (Completion, Loading))

but you’d need two additional column level constraints ensuring Completion IN ('N','Y') and similar for Loading.

Leave a Reply

Your email address will not be published. Required fields are marked *