CODEWITHSUNDEEP
sqloracle11g
H3AP
H3AP

Reputation: 1058

Multiple fields in check statement

I like to be able to enter the following values into my table: 0,0 ; 0,1 ; 1,0 But i want to disable the following value: 1,1

I have made the following query:

CREATE TABLE mytab (a CHAR(1), b CHAR(1), CHECK(a != 1 AND b != 1));

I am able to enter 0,0 but i am unable to enter 1,0. How do i rewrite this logical statement so it does fit my requirements?

Upvotes: 1

Views: 59

Answers (2)

Alex Shesterov
Alex Shesterov

Reputation: 27525

Use OR in place of AND: 

CHECK(a != 1 OR b != 1))

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269773

The check constraint should be:

check (not (a = 1 and b = 1))

or equivalently:

check (a <> 1 or b <> 1)

(Actually, these are not exactly equivalent in the presence of NULL values. The second is probably want you want if you allow NULL values.)

Upvotes: 3

Related Questions