Oracle check constraints

Question

How do you make if constraints in Oracle SQL?

Create Table A(
b varchar(25) primary key,
c varchar(25),
constraint b_1
check(b='name' and c != 'notallowed')
);

  The following should not work:

Insert into A values('name','notallowed');

But this should work:

Insert into A values('notname','notallowed');

How can I fix my constraint?

Answer 1

I guess you are looking for this

check ((b='name' and c != 'notallowed') or b != 'name')

Answer 2

If I understand correctly, the only illegal combination is b='name' and c='notallowed'. If this is correct, you can express this with the not operator:

CREATE TABLE a (
    b varchar(25) PRIMARY KEY,
    c varchar(25),
    CONSTRAINT b_1
    CHECK(NOT(b = 'name' AND c = 'notallowed'))
);

Answer 3

Another way of writing this is:

create Table A
(
  b varchar(25) primary key,
  c varchar(25),
  constraint b_1 check ( (b,c) not in (('name', 'notallowed')) )
);

(Yes, the duplicate parentheses around the IN list are required in Oracle)