-
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
-
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))
);
-
Hi akkerend,
thanks for the hint! It works perfectly like this!
Dan
