Home » SQL & PL/SQL » SQL & PL/SQL » add column not null in a not empty table
icon9.gif  add column not null in a not empty table [message #231586] Tue, 17 April 2007 11:01 Go to next message
thorin666
Messages: 144
Registered: March 2007
Senior Member

I have a problem
I am trying to modify a table adding a column with a not null value.

i tried to put
alter table tablename add constraint constname not null (column) but said

ORA-00904: : invalid identifier

Re: add column not null in a not empty table [message #231591 is a reply to message #231586] Tue, 17 April 2007 12:02 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
Are you trying to add a column with a NOT NULL constraint or modify a column to NOT NULL?

NB You cannot add a NOT NULL column to a table unless it is empty.
Re: add column not null in a not empty table [message #231592 is a reply to message #231586] Tue, 17 April 2007 12:04 Go to previous messageGo to next message
gintsp
Messages: 118
Registered: February 2007
Senior Member
If your table is not empty:
1) Add column allowing nulls
2) populate column data
3) alter table's column to not null

For syntax go to tahiti.oracle.com, pick version, browse sql reference for alter table statements

Gints Plivna
http://www.gplivna.eu
Re: add column not null in a not empty table [message #231594 is a reply to message #231586] Tue, 17 April 2007 12:07 Go to previous messageGo to next message
thorin666
Messages: 144
Registered: March 2007
Senior Member

i did that but i thinked that maybe with enable novalidate option i can use a not null constraint

i used a DEFAULT value like xxx and after that i put not null but my idea is to use the enable validate/novalidate options,

i tested with check constraints and worked but not with not null options
Re: add column not null in a not empty table [message #231595 is a reply to message #231586] Tue, 17 April 2007 12:17 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> alter table t add constraint c check (id is not null);

Table altered.

Regards
Michel
Re: add column not null in a not empty table [message #231597 is a reply to message #231595] Tue, 17 April 2007 12:21 Go to previous messageGo to next message
thorin666
Messages: 144
Registered: March 2007
Senior Member

Michel Cadot wrote on Tue, 17 April 2007 12:17
SQL> alter table t add constraint c check (id is not null);

Table altered.

Regards
Michel


yes Michel but what happen when the table has not null values?

i think is like you have a lot of numbers values and you put

alter table tablename add constraint constname check(col1=0); its a violation except you use the enable validate value (or i am wrong)

[Updated on: Tue, 17 April 2007 12:21]

Report message to a moderator

Re: add column not null in a not empty table [message #231608 is a reply to message #231597] Tue, 17 April 2007 12:49 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I should add:
SQL> select count(*) from t;
  COUNT(*)
----------
         3

1 row selected.

This is not a lot of rows but one or many is the same for Oracle: it check all of them:
SQL> alter table t add constraint c2 check (id=0);
alter table t add constraint c2 check (id=0)
                             *
ERROR at line 1:
ORA-02293: cannot validate (MICHEL.C2) - check constraint violated


SQL> alter table t add constraint c2 check (id!=0);

Table altered.


Regards
Michel

Re: add column not null in a not empty table [message #231609 is a reply to message #231586] Tue, 17 April 2007 12:56 Go to previous message
thorin666
Messages: 144
Registered: March 2007
Senior Member

i didn't know about "id!=something"

but you teach me something

alter table t add constraint c check (id is not null); and i can enable novalidate that constraint

i used (wrong)
alter table t add constraint c not null (id); and oracle show me ORA-00904: : invalid identifier

maybe the correct way is to use your way (using checks)

thnx so muchs michael like everyday you teach us very valuables things Wink
Previous Topic: Regarding Trigger
Next Topic: Insert missing row
Goto Forum:
  


Current Time: Sun Dec 04 00:44:01 CST 2016

Total time taken to generate the page: 0.21953 seconds