Home » SQL & PL/SQL » SQL & PL/SQL » How can we change not null to null!! (oracle 9i,windows xp)
How can we change not null to null!! [message #336145] Fri, 25 July 2008 00:44 Go to next message
shanthakumar.utt
Messages: 15
Registered: May 2008
Location: bangalore
Junior Member
Hi

I have a table called allot in that one column having
reference_no var char2(10) default '0' not null;
and i want to alter the column to remove default '0' and modify not null into null (reference_no var char2(10) null),
please help me .

Thanks in advance
Re: How can we change not null to null!! [message #336151 is a reply to message #336145] Fri, 25 July 2008 00:53 Go to previous messageGo to next message
Michel Cadot
Messages: 64119
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> create table t (col varchar2(10) default '0' not null);

Table created.

SQL> select nullable, data_default from user_tab_columns where table_name='T';
N DATA_DEFAULT
- --------------------------------------------------
N '0'

1 row selected.

SQL> alter table t modify (col default null null);

Table altered.

SQL> select nullable, data_default from user_tab_columns where table_name='T';
N DATA_DEFAULT
- --------------------------------------------------
Y null

1 row selected.

Regards
Michel
Re: How can we change not null to null!! [message #336152 is a reply to message #336151] Fri, 25 July 2008 01:03 Go to previous messageGo to next message
shanthakumar.utt
Messages: 15
Registered: May 2008
Location: bangalore
Junior Member
Thanks michel

But when we select after modifying the column in default it will be 'null' value but i want even 'null' to be removed from default how can i do it.

Regards

Shanthu

Re: How can we change not null to null!! [message #336153 is a reply to message #336152] Fri, 25 July 2008 01:09 Go to previous messageGo to next message
Michel Cadot
Messages: 64119
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It is not possible.
But who care? What is important is that the column is nullable and default value is null.

Regards
Michel

[Updated on: Fri, 25 July 2008 01:09]

Report message to a moderator

Re: How can we change not null to null!! [message #336160 is a reply to message #336153] Fri, 25 July 2008 01:21 Go to previous messageGo to next message
shanthakumar.utt
Messages: 15
Registered: May 2008
Location: bangalore
Junior Member
ok thanks michel
Re: How can we change not null to null!! [message #336163 is a reply to message #336151] Fri, 25 July 2008 01:29 Go to previous messageGo to next message
shanthakumar.utt
Messages: 15
Registered: May 2008
Location: bangalore
Junior Member
Sorry michel i am interupting u when i have tried like this
alter table allot modify reference_no default null null);

it is showing error stating that

ORA-01451: column to be modified to NULL cannot be modified to NULL

i have already values in that column is that reason it is showing error

Regards

shanthu
Re: How can we change not null to null!! [message #336169 is a reply to message #336163] Fri, 25 July 2008 01:43 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Quote:

SQL> !oerr ora 1451
01451, 00000, "column to be modified to NULL cannot be modified to NULL"
// *Cause: the column may already allow NULL values, the NOT NULL constraint
// is part of a primary key or check constraint.
// *Action: if a primary key or check constraint is enforcing the NOT NULL
// constraint, then drop that constraint.


Hope this helps.

Regards

Raj
Re: How can we change not null to null!! [message #336170 is a reply to message #336169] Fri, 25 July 2008 01:49 Go to previous messageGo to next message
shanthakumar.utt
Messages: 15
Registered: May 2008
Location: bangalore
Junior Member
hi i can't drop the column because i am having a values in that
for that reason only i am asking if not i could have drop the column.

Regards,

shanthu
Re: How can we change not null to null!! [message #336172 is a reply to message #336170] Fri, 25 July 2008 01:52 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Quote:

hi i can't drop the column because i am having a values in that
for that reason only i am asking if not i could have drop the column.

I don't think I mentioned anywhere to drop the column. Re-read my post again and this time could you please post the complete table structure.

Regards

Raj
Re: How can we change not null to null!! [message #336206 is a reply to message #336152] Fri, 25 July 2008 03:24 Go to previous messageGo to next message
Littlefoot
Messages: 20896
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
shanthakumar.utt
But when we select after modifying the column in default it will be 'null' value but i want even 'null' to be removed from default how can i do it.


Oracle documentation says that you can't do that:
Quote:
If a column has a default value, then you can use the DEFAULT clause to change the default to NULL, but you cannot remove the default value completely. That is, if a column has ever had a default value assigned to it, then the DATA_DEFAULT column of the USER_TAB_COLUMNS data dictionary view will always display either a default value or NULL.
Re: How can we change not null to null!! [message #336230 is a reply to message #336206] Fri, 25 July 2008 06:04 Go to previous messageGo to next message
jramya
Messages: 42
Registered: April 2008
Member
Hi Shantakumar,
When I had a simillar issue,I did the following
1.Created another column(C2) with same data type as of this column(Column having null constraints C1).
2.Apply new null,default constraints on C2
3.Copy values from C1 to C2
Update Tablename set C2=C1
4. Drop column C1 and rename C2 as C1.

I hope the above steps can be applied if have oracle 9+
Regards
Ramya
Re: How can we change not null to null!! [message #336355 is a reply to message #336230] Fri, 25 July 2008 15:23 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
jramya told how he wasted a perfect good afternoon on doing unnecessary stuff
Hi Shantakumar,
When I had a simillar issue,I did the following

Why?
What is the net difference between a column with a default NULL value and a column without one?
If you really hate having afternoons off, I know some other useless stuff to do.
Let me guess, you also rebuild indexes on a regular basis, right?
Previous Topic: Can we Write the following Insert Statement
Next Topic: table copy from user to user
Goto Forum:
  


Current Time: Tue Dec 06 10:23:51 CST 2016

Total time taken to generate the page: 0.11379 seconds