469,281 Members | 2,484 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,281 developers. It's quick & easy.

How to add not null constraint to existing table?

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
Aug 11 '07 #1
5 110762
debasisdas
8,127 Expert 4TB
NOT NULL constraint can't be added at table level

to add the constraint u need to modify the column

Expand|Select|Wrap|Line Numbers
  1. alter table emp modify ename not null;
Aug 11 '07 #2
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
Aug 20 '07 #3
hi why "NOT NULL" is only constraint which cannot be enforced at table level?
Sep 28 '10 #4
amitpatel66
2,367 Expert 2GB
You can do so by using below ALTER statement:

Expand|Select|Wrap|Line Numbers
  1.  
  2. ALTER TABLE emp MODIFY name NOT NULL
  3. /
  4.  
  5.  
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:

Expand|Select|Wrap|Line Numbers
  1.  
  2. SQL> create table my_test(col1 NUMBER);
  3.  
  4. Table created.
  5.  
  6. SQL> ed
  7. Wrote file afiedt.buf
  8.  
  9.   1* insert into my_test values(1)
  10. SQL> /
  11.  
  12. 1 row created.
  13.  
  14. SQL> ed
  15. Wrote file afiedt.buf
  16.  
  17.   1* insert into my_test values(NULL)
  18. SQL> /
  19.  
  20. 1 row created.
  21.  
  22. SQL> commit;
  23.  
  24. Commit complete.
  25.  
  26. SQL> alter table my_Test modify col1 NOT NULL;
  27. alter table my_Test modify col1 NOT NULL
  28. *
  29. ERROR at line 1:
  30. ORA-02296: cannot enable (APPS.) - null values found
  31.  
  32.  
  33. SQL> delete from my_test WHERE col1 IS NULL;
  34.  
  35. 1 row deleted.
  36.  
  37. SQL> COMMIT;
  38.  
  39. Commit complete.
  40.  
  41. SQL> alter table my_Test modify col1 NOT NULL;
  42.  
  43. Table altered.
  44.  
  45. SQL> desc my_test;
  46.  Name           Null?    Type
  47.  -------------- -------- ---------
  48.  COL1           NOT NULL NUMBER
  49.  
  50. SQL> 
  51.  
  52.  
Sep 29 '10 #5
"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
;
Dec 12 '11 #6

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

5 posts views Thread by annecarterfredi | last post: by
1 post views Thread by femina | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.