1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Applying CHECK Constraint

Discussion in 'SQL PL/SQL' started by Tariq Bashir Malhi, Aug 12, 2010.

  1. Tariq Bashir Malhi

    Tariq Bashir Malhi Forum Advisor

    Messages:
    44
    Likes Received:
    0
    Trophy Points:
    80
    I want to apply a constraint on a table, if column c1 or c2 is not null then c3 should also be not null. I try but receive ORA-02438 error message. Help me resolving this .....
     
  2. Tariq Bashir Malhi

    Tariq Bashir Malhi Forum Advisor

    Messages:
    44
    Likes Received:
    0
    Trophy Points:
    80
    Re: Applying CHECH Contsraint

    For general benefit, i am sharing following solutions from which i get from OTN forum

    Solution 1.

    Code (SQL):
    SQL> CREATE TABLE t
    (
       c1   INT,
       c2   INT,
       c3   INT
    )
    /
    TABLE created.
     
    SQL> ALTER TABLE t ADD CONSTRAINT t_chk CHECK (CASE WHEN c1 IS NOT NULL OR c2 IS NOT NULL THEN c3 ELSE 1 END IS NOT NULL)
    /
    TABLE altered.
     
    SQL> INSERT INTO t VALUES (NULL, NULL, NULL)
    /
    1 ROW created.
     
    SQL> INSERT INTO t VALUES (1, NULL, NULL)
    /
    INSERT INTO t VALUES (1, NULL, NULL)
    Error at line 17
    ORA-02290: CHECK CONSTRAINT (MICHAEL.T_CHK) violated
     
    SQL> INSERT INTO t VALUES (NULL, 2, NULL)
    /
    INSERT INTO t VALUES (NULL, 2, NULL)
    Error at line 19
    ORA-02290: CHECK CONSTRAINT (MICHAEL.T_CHK) violated
     
    SQL> INSERT INTO t VALUES (NULL, 2, 3)
    /
    1 ROW created.
     
    SQL> INSERT INTO t VALUES (1, 2, NULL)
    /
    INSERT INTO t VALUES (1, 2, NULL)
    Error at line 23
    ORA-02290: CHECK CONSTRAINT (MICHAEL.T_CHK) violated
    Solution 2.

    Code (SQL):
    SQL> CREATE TABLE tbl (col1 VARCHAR2(1),col2 VARCHAR2(1),col3 VARCHAR2(1));
     
    TABLE created.
     
    SQL> CREATE OR REPLACE TRIGGER my_trig1 BEFORE INSERT ON tbl
      2  FOR each ROW
      3  BEGIN
      4  IF (:NEW.col1 IS NOT NULL OR :NEW.col2 IS NOT NULL) THEN
      5  IF (:NEW.col3 IS NULL) THEN
      6  RAISE_APPLICATION_ERROR(-20000,'Col3 cannot be Null when Col1 or Col2 is not null');
      7  END IF;
      8  END IF;
      9  END;
     10  /
     
    TRIGGER created.
     
    SQL> INSERT INTO tbl VALUES(NULL,NULL,NULL);
     
    1 ROW created.
     
    SQL> commit;
     
    Commit complete.
     
    SQL> INSERT INTO tbl VALUES(1,NULL,NULL);
    INSERT INTO tbl VALUES(1,NULL,NULL)
                *
    ERROR at line 1:
    ORA-20000: Col3 cannot be NULL WHEN Col1 OR Col2 IS NOT NULL
    ORA-06512: at "APPS.MY_TRIG1", line 4
    ORA-04088: error during execution OF TRIGGER 'APPS.MY_TRIG1'
     
  3. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    Re: Applying CHECH Contsraint

    You can use either of the two constraints as metioned below.


    Code (SQL):
    SQL> CREATE TABLE T1
      2  ( C1 NUMBER ,
      3    C2 NUMBER,
      4    C3 NUMBER
      5  );

    TABLE created.

    SQL> ALTER TABLE T1
      2  ADD CONSTRAINT T1_CHK
      3     CHECK (
      4           ( NVL(C1,C2) IS NOT NULL   AND
      5             C3 IS NOT NULL   )  OR
      6             (NVL(C1,C2) IS  NULL   AND
      7             (C3 IS  NULL OR C3 IS NOT   NULL))
      8            );

    TABLE altered.

    SQL> SELECT 1, 2, 3 FROM DUAL UNION
      2  SELECT 1, NULL, 3 FROM DUAL UNION
      3  SELECT NULL, NULL, 3 FROM DUAL UNION
      4  SELECT NULL, NULL, NULL FROM DUAL;

             1          2          3
    ---------- ---------- ----------
             1          2          3
             1                     3
                                   3


    SQL> INSERT INTO T1
      2  SELECT 1, 2, 3 FROM DUAL UNION
      3      SELECT 1, NULL, 3 FROM DUAL UNION
      4      SELECT NULL, NULL, 3 FROM DUAL UNION
      5      SELECT NULL, NULL, NULL FROM DUAL;

    4 ROWS created.

    SQL> INSERT INTO T1
      2  SELECT 1, NULL, NULL FROM DUAL;
    INSERT INTO T1
    *
    ERROR at line 1:
    ORA-02290: CHECK CONSTRAINT (SCOTT.T1_CHK) violated


    SQL> INSERT INTO T1
      2  SELECT NULL, 2, NULL FROM DUAL;
    INSERT INTO T1
    *
    ERROR at line 1:
    ORA-02290: CHECK CONSTRAINT (SCOTT.T1_CHK) violated


    SQL> ROLLBACK;

    ROLLBACK complete.

    SQL> ALTER TABLE T1
      2  DROP CONSTRAINT T1_chk;

    TABLE altered.

    SQL> ALTER TABLE T1
      2  ADD CONSTRAINT T1_CHK
      3     CHECK (
      4     NVL2(NVL(C1,C2),C3,0) IS NOT NULL
      5    );

    TABLE altered.

    SQL> INSERT INTO T1
      2      SELECT 1, 2, 3 FROM DUAL UNION
      3      SELECT 1, NULL, 3 FROM DUAL UNION
      4      SELECT NULL, NULL, 3 FROM DUAL UNION
      5      SELECT NULL, NULL, NULL FROM DUAL
      6  ;

    4 ROWS created.

    SQL> INSERT INTO T1
      2  SELECT 1, NULL, NULL FROM DUAL;
    INSERT INTO T1
    *
    ERROR at line 1:
    ORA-02290: CHECK CONSTRAINT (SCOTT.T1_CHK) violated


    SQL> INSERT INTO T1
      2  SELECT NULL, 2, NULL FROM DUAL;
    INSERT INTO T1
    *
    ERROR at line 1:
    ORA-02290: CHECK CONSTRAINT (SCOTT.T1_CHK) violated


    SQL>
     
     
  4. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    Re: Applying CHECH Contsraint

    Oops.. I didn't see your reply.

    Anyway Trigger should not be used for the scenario where normal constraints could be used. And moreover, trigger in your example should be modified to handle the update also from application context.