Home » SQL & PL/SQL » SQL & PL/SQL » How to remove not null constraint from a column
How to remove not null constraint from a column [message #323367] Wed, 28 May 2008 05:43 Go to next message
ShaShalini
Messages: 59
Registered: January 2007
Member
Hi all

I want to remove a not null contraint from a column . I am using oracle 10g.

I already tried

ALTER TABLE roc_f13_allottees MODIFY (RF13A_ID_NO varchar2(14) null);

but when I describe the table I find the not null constraint still there .

Thanks in advance
Re: How to remove not null constraint from a column [message #323373 is a reply to message #323367] Wed, 28 May 2008 05:57 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
SQL> create table nn_test (col_1 date constraint nn_test_col_1_nn not null);

Table created.

SQL> 
SQL> select table_name,constraint_name,search_condition from user_constraints where table_name = 'NN
_TEST';

TABLE_NAME                     CONSTRAINT_NAME
------------------------------ ------------------------------
SEARCH_CONDITION
--------------------------------------------------------------------------------
NN_TEST                        NN_TEST_COL_1_NN
"COL_1" IS NOT NULL


SQL> 
SQL> alter table nn_test drop constraint NN_TEST_COL_1_NN;

Table altered.

SQL> 
SQL> select * from user_constraints where table_name = 'NN_TEST';

no rows selected
Re: How to remove not null constraint from a column [message #323378 is a reply to message #323367] Wed, 28 May 2008 06:02 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> create table t (col number not null);

Table created.

SQL> desc t
 Name                             Null?    Type
 -------------------------------- -------- ----------------------
 COL                              NOT NULL NUMBER

SQL> alter table t modify (col null);

Table altered.

SQL> desc t
 Name                             Null?    Type
 -------------------------------- -------- ----------------------
 COL                                       NUMBER

Regards
Michel
Re: How to remove not null constraint from a column [message #323400 is a reply to message #323378] Wed, 28 May 2008 06:56 Go to previous messageGo to next message
ShaShalini
Messages: 59
Registered: January 2007
Member
Michel

I tried this statement again

ALTER TABLE roc_f13_allottees MODIFY (RF13A_ID_NO null)
*

ERROR at line 1:
ORA-01451: column to be modified to NULL cannot be modified to NULL


And got this error. And when am describing the table , am still getting the ID_no fields as not null .
Re: How to remove not null constraint from a column [message #323401 is a reply to message #323400] Wed, 28 May 2008 06:59 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
So, which of these apply to your column:

ORA-01451 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, or an ALTER TABLE MODIFY statement attempted to change a column specification unnecessarily, from NULL to NULL.
Re: How to remove not null constraint from a column [message #323428 is a reply to message #323401] Wed, 28 May 2008 08:12 Go to previous messageGo to next message
joy_division
Messages: 4642
Registered: February 2005
Location: East Coast USA
Senior Member
JRow, what version of Oracle is your message from? I get this from SQL*Plus: Release 10.1.0.4.2 - Production on Wed May 28 08:10:48 2008
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.


I was wondering why the ambiguity was in your OERR. It seems to me that the first and third explanations are the same, or am I reading it wrong.

Re: How to remove not null constraint from a column [message #323473 is a reply to message #323428] Wed, 28 May 2008 09:40 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Oracle's 9i R2 online docs
Re: How to remove not null constraint from a column [message #323662 is a reply to message #323367] Thu, 29 May 2008 03:04 Go to previous messageGo to next message
ShaShalini
Messages: 59
Registered: January 2007
Member
Thanks all for your help

Actually the field was part of a primary key thats why I was not able to see the change.

I queried the ALL_CONS_COLUMNS then came to know that the field was part of a primary key.

Sorry

Re: How to remove not null constraint from a column [message #324006 is a reply to message #323662] Fri, 30 May 2008 04:49 Go to previous message
dr.s.raghunathan
Messages: 540
Registered: February 2008
Senior Member
since it is 10G disable on column level also helps
Previous Topic: database copying
Next Topic: Marked killed sessions and forms not terminated
Goto Forum:
  


Current Time: Thu Dec 08 00:07:01 CST 2016

Total time taken to generate the page: 0.07236 seconds