0

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

ALTER TABLE Table_Name
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

  • 1
    check (not (Completion = 'N' and Loading = 'N')) Or simply change your and to or: check (Completion <> 'N' OR Loading <> 'N') – ypercubeᵀᴹ Apr 1 '15 at 15:55
  • @ypercube thank you so much it worked perfect. – B K Apr 1 '15 at 16:15
2

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.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.