How to add not null constraint to existing table?

[ganeshshegde]

How to add not null constrint to an existing table?


i tried with "alter table emp add constraint not_null_name not null(name)" command
but it's giving error ..........

alter table emp add constraint not_null_name not null(name)
*

ERROR at line 1:
ORA-00904: : invalid identifier

[debasisdas]

NOT NULL constraint can't be added at table level

to add the constraint u need to modify the column

alter table emp modify ename not null;

[holdingbe]

Hi,
If u need add any constraints to tables means you write like

alter table table_name
add constraints

if u need add not null constraints means

alter table table_name
modify constraints

[hhhhhhh]

hi why "NOT NULL" is only constraint which cannot be enforced at table level?

[amitpatel66]

You can do so by using below ALTER statement:

 
ALTER TABLE emp MODIFY name 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:

 
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> 
 
 

[iinnnnoouutt]

"add constraint" only for out-of-line constraints
while not-null constraint is inline constraint,
so cannot use "add constraint" for not-null constraint
ie. to add inline constraint uses modify column

alter table emp modify
column_abc_name constraint not_null_column_abc_name not null
;