Help on constraints
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: Help on constraints

  1. #1
    Join Date
    Aug 2000
    Posts
    11
    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

  2. #2
    Join Date
    Jul 2000
    Posts
    296
    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))
    );

  3. #3
    Join Date
    Aug 2000
    Posts
    11
    Hi akkerend,

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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width