4 Replies Latest reply on Jul 6, 2012 9:54 AM by karteek

    regarding check constraint

    karteek
      Hi everyone,

      i have a table for example :

      CREATE TABLE RMD_2
      (
      "RMD_ID" NUMBER(10,0) NOT NULL ENABLE,
      "ABB" VARCHAR2(16 BYTE),
      "ACT_IND" VARCHAR2(1 BYTE) NOT NULL DISABLE,
      CONSTRAINT "RMD_2_C1" CHECK (act_ind IN ('Y', 'N')) ENABLE
      )

      for column ACT_IND their is a check constraint RMD_2_C1
      it should only accept either Y or N.

      but, when ever i am inserting other values other than Y or N it is accepting

      it should not accept and it should have to give error message ORA-02293- : check constraint violated.


      but it is not happening,it is accepting all other values even NULL value also.
      my requirement is this column should only accept either Y or N and it should not accept other values.



      please needhelpful.

      Edited by: karteek on Jul 5, 2012 7:00 AM
        • 1. Re: regarding check constraint
          Ashish_Apps
          request you to put a check constraint on rmd_id like rmd_id < 5 and see if it works
          • 2. Re: regarding check constraint
            Solomon Yakobson
            Based on table definition check contraint will allow values N, Y only. But it will also allow NULL:
            SQL> insert
              2    into rmd_2
              3    values(1,'A','X')
              4  /
            insert
            *
            ERROR at line 1:
            ORA-02290: check constraint (SCOTT.RMD_2_C1) violated
            
            
            SQL> insert
              2    into rmd_2
              3    values(1,'A',NULL)
              4  /
            
            1 row created.
            
            SQL> insert
              2    into rmd_2
              3    values(1,'A','Y')
              4  /
            
            1 row created.
            
            SQL> insert
              2    into rmd_2
              3    values(1,'A','N')
              4  /
            
            1 row created.
            Change column ACT_IND from NOT NULL DISABLE to NOT NULL ENABLE. Eithe find system generated NOT NULL constraint name and enable it or, which might be simpler:
            SQL> alter table rmd_2
              2    modify act_ind null
              3  /
            
            Table altered.
            
            SQL> alter table rmd_2
              2    modify act_ind not null enable
              3  /
            
            Table altered.
            
            SQL> 
            Now:
            SQL> insert
              2    into rmd_2
              3    values(1,'A','X')
              4  /
            insert
            *
            ERROR at line 1:
            ORA-02290: check constraint (SCOTT.RMD_2_C1) violated
            
            
            SQL> insert
              2    into rmd_2
              3    values(1,'A',NULL)
              4  /
              values(1,'A',NULL)
                           *
            ERROR at line 3:
            ORA-01400: cannot insert NULL into ("SCOTT"."RMD_2"."ACT_IND")
            
            
            SQL> insert
              2    into rmd_2
              3    values(1,'A','Y')
              4  /
            
            1 row created.
            
            SQL> insert
              2    into rmd_2
              3    values(1,'A','N')
              4  /
            
            1 row created.
            
            SQL> 
            SY.
            1 person found this helpful
            • 3. Re: regarding check constraint
              Paul  Horth
              Works for me.

              >
              insert into rmd_2
              values(123,'ABC','Z')

              ORA-02290: check constraint (HORTHP.RMD_2_C1) violated

              >

              Give example insert and also DB version you are using.
              • 4. Re: regarding check constraint
                karteek
                Hi Solomon,

                Thanks for your quick Response, i came to know that by using check constraint it will also Accept NULL Values.
                hence for check constraints column it must and should be a NOT NULL constraint column. then only it will not accept NULL Values into this table.

                Thanks to all.