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!

Constraints.,

Discussion in 'General' started by Vicky, Feb 12, 2014.

  1. Vicky

    Vicky Forum Advisor

    Messages:
    261
    Likes Received:
    11
    Trophy Points:
    260
    Location:
    Chennai
    How to add a not null constraint to an existing table???
     
  2. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    Vicky likes this.
  3. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula
    Hi,

    You can do so by using below ALTER statement:

    ALTER TABLE emp MODIFY ename NOT NULL;

    Make sure there are no existing NULL values in the column name, else you will not be able to ENABLE NOT NULL constraint.

    Check below:

    Code (SQL):
     SQL> CREATE TABLE my_test(col1 NUMBER);
     
    TABLE created.
     
    SQL> ed
    Wrote file afiedt.buf
     
      1* INSERT INTO my_test VALUES(1)
    SQL> /
     
    1 ROW created.
     
    SQL> ed
    Wrote file afiedt.buf
     
      1* INSERT INTO my_test VALUES(NULL)
    SQL> /
     
    1 ROW created.
     
    SQL> commit;
     
    Commit complete.
     
    SQL> ALTER TABLE my_Test MODIFY col1 NOT NULL;
    ALTER TABLE my_Test MODIFY col1 NOT NULL
    *
    ERROR at line 1:
    ORA-02296: cannot enable (APPS.) - NULL VALUES found
     
     
    SQL> DELETE FROM my_test WHERE col1 IS NULL;
     
    1 ROW deleted.
     
    SQL> COMMIT;
     
    Commit complete.
     
    SQL> ALTER TABLE my_Test MODIFY col1 NOT NULL;
     
    TABLE altered.
     
    SQL> DESC my_test;
     Name           NULL?    TYPE
     -------------- -------- ---------
     COL1           NOT NULL NUMBER
     
    SQL>
     
     
    Vicky and Bharat like this.