OraFAQ Forum: SQL & PL/SQL » Alter a populated table to add a not null field.

Home » SQL & PL/SQL » SQL & PL/SQL » Alter a populated table to add a not null field.

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Alter a populated table to add a not null field. [message #35993]

Mon, 29 October 2001 07:32

Poonamb
Messages: 16
Registered: October 2001

Junior Member

Hello,

This is actually an SQL question but that message board is giving errors!

I need to alter a table that already has data to include an additional not null field.

I decided to first add the field to the table as not null
ALTER TABLE CONCEPT
ADD (CONCEPT_SUMM VARCHAR2(300));

Then populate the field with desired value and then
add contraint to make it not null.

ALTER TABLE CONCEPT
add constraint NL_CONCEPT_C_SUMM
check ("CONCEPT_SUMM" IS NOT NULL);

This works! Except that when I describe the table after both steps, I see the new field and the new constraint. However, next to the field, it does not show not null. WHY ????

Thanks in advance,
Poonam

----------------------------------------------------------------------

Report message to a moderator

Re: Alter a populated table to add a not null field. [message #35995 is a reply to message #35993]

Mon, 29 October 2001 09:49

Todd Barry
Messages: 4819
Registered: August 2001

Senior Member

Instead of adding a CHECK constraint to handle this, just alter the column definition:

alter table t
modify (concept_summ not null);

I would then drop the CHECK constaint.

----------------------------------------------------------------------

Report message to a moderator

Previous Topic:	storing a negative numerical value in a field of data type numeric
Next Topic:	URGENT : Help needed for Stored procedures

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Nov 26 15:12:32 CST 2024