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!

Constraint problem

Discussion in 'SQL PL/SQL' started by waheeb, Oct 13, 2016.

  1. waheeb

    waheeb Active Member

    Messages:
    26
    Likes Received:
    0
    Trophy Points:
    80
    Location:
    yemen
    Hi,
    I created the Schools table as follows :

    Create table schools(School_id number(10) primary key ,school_name varchar2(200) ,phone_number number(10) ,address varchar2(200),status number(1) check(status in(1,0)));

    Now,the table has data inserted already and it seems that the STATUS column accepts NULL values which it should not.How can I alter it not to accept NULL values and at the same time STILL only accepts values of 0 and 1 only?

    Thanks
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,345
    Likes Received:
    347
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    First you populate the column with the desired value that isn't NULL, then you alter your table definition for the desired column to set it to NOT NULL:

    alter table schools modify status number(1) not null;

    Your check constraint should not be affected. To prove this:

    Code (SQL):
    SQL> CREATE TABLE schools(School_id NUMBER(10) PRIMARY KEY ,school_name varchar2(200) ,phone_number NUMBER(10) ,address varchar2(200),STATUS NUMBER(1) CHECK(STATUS IN(1,0)));

    TABLE created.

    SQL>
    SQL> BEGIN
      2
      3     FOR i IN 1..1000 loop
      4             INSERT INTO schools
      5             VALUES(i,'Elrond School Number '||i, 9999999999, '14 15th Street, Bangobongo, Snerm',NULL);
      6     END loop;
      7
      8     commit;
      9  END;
    10  /

    PL/SQL PROCEDURE successfully completed.

    SQL>
    SQL> UPDATE schools SET STATUS= 1 WHERE STATUS IS NULL;

    1000 ROWS updated.

    SQL> ALTER TABLE schools MODIFY STATUS NUMBER(1) NOT NULL;

    TABLE altered.

    SQL>
    SQL> INSERT INTO schools
      2  VALUES(1001,'Elrond School Number 1001', 9999999999, '14 15th Street, Bangobongo, Snerm',NULL);
    VALUES(1001,'Elrond School Number 1001', 9999999999, '14 15th Street, Bangobongo, Snerm',NULL)
                                                                                             *
    ERROR at line 2:
    ORA-01400: cannot INSERT NULL INTO ("BING"."SCHOOLS"."STATUS")


    SQL>
    SQL> INSERT INTO schools
      2  VALUES(1001,'Elrond School Number 1001', 9999999999, '14 15th Street, Bangobongo, Snerm',0);

    1 ROW created.

    SQL>
     
     
    waheeb likes this.
  3. waheeb

    waheeb Active Member

    Messages:
    26
    Likes Received:
    0
    Trophy Points:
    80
    Location:
    yemen

    It works.Thank you.