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!

How to add not null constraint to an existing table having null values in the respect

Discussion in 'SQL PL/SQL' started by Vicky, Aug 14, 2014.

  1. Vicky

    Vicky Forum Advisor

    Messages:
    261
    Likes Received:
    11
    Trophy Points:
    260
    Location:
    Chennai
    How to add NOT NULL constraint to a column in an existing table which already have null values in the respective columns..,?!
     
  2. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    Re: How to add not null constraint to an existing table having null values in the res

    Hi.
    Simple example :
    Code (SQL):

    DROP TABLE test_constraint purge;
    CREATE TABLE test_constraint
    (id INT PRIMARY KEY,
     y  INT
     );
     INSERT INTO test_constraint
     SELECT level,
         NULLIF(MOD(level,2),0)
     FROM dual CONNECT BY level < 7;
     commit;
    ALTER TABLE test_constraint MODIFY  y NOT NULL novalidate;
    SELECT * FROM test_constraint;
    INSERT INTO test_constraint VALUES (-999,NULL);

    SQL>

    TABLE dropped
     
    TABLE created
     
    6 ROWS inserted
     
    Commit complete
     
    TABLE altered
     
                                         ID                                       Y
    --------------------------------------- ---------------------------------------
                                          1                                       1
                                          2
                                          3                                       1
                                          4
                                          5                                       1
                                          6
     
    6 ROWS selected
     
    INSERT INTO test_constraint VALUES (-999,NULL)
     
    ORA-01400: cannot INSERT NULL INTO ("PRKNEW_DATA"."TEST_CONSTRAINT"."Y")