Help on constraints

[Dan]

Hi,

I have a problem with integrity constraints. I have a table with two columns:

CREATE TABLE Constraint_Demo
(Item_1 VARCHAR2(10),
Item_2 NUMBER(10),
CONSTRAINT Tuple_Check CHECK ((Item_1 = 'A' AND Item_2 != Null) OR (Item_1 = 'B' AND Item_2 = Null))
);

If 'A' is inserted in the first column, then a number must be inserted in the second column. On the other hand, if 'B' is inserted in the first column, there must be inserted NULL in the second column. Example:

Item_1 | Item_2
-----------------
A | 1 --> ok
B | NULL --> ok
A | NULL --> Not allowed!
B | 1 --> Not allowed!

However, the following statements are ok:
insert into Constraint_Demo values ('A', NULL);
insert into Constraint_Demo values ('', 1);

Why? And what is the correct syntax?

Dan

[akkerend]

With NULL don't use '= NULL' or '!= NULL', but use 'IS NULL' or 'IS NOT NULL'. Check item_1 is not null.
Comparisons involving nulls always yield NULL , OR returns TRUE only if either one of the operands is TRUE, AND returns TRUE only if both operands are true.

CREATE TABLE Constraint_Demo
(Item_1 VARCHAR2(10) NOT NULL,
Item_2 NUMBER(10),
CONSTRAINT Tuple_Check CHECK ((Item_1 = 'A' AND Item_2 IS NOT Null) OR (Item_1 = 'B' AND Item_2 IS Null))
);

[Dan]

Hi akkerend,

thanks for the hint! It works perfectly like this!
Dan