Search

Join this Blog for direct reference of any post into your inbox. To join just click on "Join this Site" under "Follower" then Login with your Email.*** DECLARATION: I Maintain this Blog for Helping Myself While at Work and Welcome any body Needing Help!!!.*** CAUTION: Using any of the script from this Blog may contain at Own Risk. These scripts May or May not have been Tested.***

Sunday, 17 June 2012

Adding NOT NULL column in a table

The following shows how to add in a table a new column that is NOT NULL when a table already has rows without providing a default value.
SQL> connect shahid1/ahmed@orcl3;
Connected.
SQL> CREATE TABLE EXAMPLE
  2  (ENO NUMBER(1));
Table created.
SQL> INSERT INTO EXAMPLE VALUES(1);
1 row created.
SQL> COMMIT;
Commit complete.
Now we will add new column without the NULL constraint and then you have to modify the column to be NOT NULL using NOVALIDATE
SQL> ALTER TABLE EXAMPLE ADD MGR NUMBER (2);
Table altered.
Now if you describe the table it will not show the NOT NULL constraint on the column ENAME yet
SQL> DESC EXAMPLE;
 Name                          Null?    Type
----------------------        -------- -----------
 ENO                                   NUMBER(1)
 MGR                                   NUMBER(2)
As shown below example even though not shown the NOT NULL constraint (in desc table) is there as new values cannot be inserted in the table that are NULL.
SQL> ALTER TABLE EXAMPLE MODIFY MGR NOT NULL NOVALIDATE;
Table altered.
SQL> DESC EXAMPLE;
 Name                          Null?    Type
----------------------------- -------- ---------
 ENO                                   NUMBER(1)
 MGR                                   NUMBER(2)
SQL> INSERT INTO EXAMPLE VALUES(1, NULL);
INSERT INTO EXAMPLE VALUES(1, NULL)
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at line 8
ORA-01400: cannot insert NULL into ("SHAHID1"."EXAMPLE"."MGR")
Now we can enable the constraint by updating the rows that have NULL value so we need to find the constraint name
SQL> select column_name, constraint_name from dba_cons_columns where table_name = 'EXAMPLE' and owner = 'SHAHID1';
COLUMN_NAME CONSTRAINT_NAME
----------- --------------
MGR         SYS_C007659
Now try this way...
SQL> UPDATE SHAHID1.EXAMPLE SET MGR=99 WHERE MGR IS NULL;
1 row updated.
SQL> SELECT * FROM EXAMPLE;
       ENO        MGR
---------- ----------
         1         99
SQL> COMMIT;
Commit complete.
SQL> Alter table SHAHID1.EXAMPLE modify constraint SYS_C007659 validate;
Table altered.
SQL> DESC EXAMPLE;
 Name                        Null?    Type
 --------------------------- -------- -----------
 ENO                                  NUMBER(1)
 MGR                         NOT NULL NUMBER(2)
From the below try it is clear we can't add a new column “NOT NULL” as table is not empty
SQL> ALTER TABLE EXAMPLE ADD SALARY NUMBER(10,2) NOT NULL;
ALTER TABLE EXAMPLE ADD SALARY NUMBER(10,2) NOT NULL
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at line 8
ORA-01758: table must be empty to add mandatory (NOT NULL) column
Also with NOVALIDATE as table is not empty
SQL> ALTER TABLE EXAMPLE ADD SALARY NUMBER(10,2) NOT NULL novalidate;
ALTER TABLE EXAMPLE ADD SALARY NUMBER(10,2) NOT NULL novalidate
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at line 8
ORA-01758: table must be empty to add mandatory (NOT NULL) column

1 comments: