How to drop Not Null constraint (system generated)

[dgirdhar]

Hello,

I created one table say "Table1" with one column say "Column1" with constraint Not Null. Oracle generated its own name for this constraint. Say SYS_C#########.

I want to delete this constraint using one sql script. But how i will find out the constraint name to drop this constraint?

-Dhiraj

[chandu031]

Hello,

I created one table say "Table1" with one column say "Column1" with constraint Not Null. Oracle generated its own name for this constraint. Say SYS_C#########.

I want to delete this constraint using one sql script. But how i will find out the constraint name to drop this constraint?

-Dhiraj

Hi,

All the Not null constraints will be stored in a table called ALL_CONS_COLUMNS which has Owner, table_name,column_name,constraint_name and position(in case of primary key)

So to get the constraint name for just a NOT NULL constraint your query would look like this :

 
SELECT CONSTRAINT_NAME FROM ALL_CONS_COLUMNS WHERE OWNER = <user_name> AND TABLE_NAME = <table_name>
AND COLUMN_NAME = <column_name> 
 

[frozenmist]

Hi,
A simple solution that I can think of if you know the column name is that
you can just alter the column to allow null. This would take out the constraint , wouldn't it?
If you want the constraint name as such, it would be better if you do what Chandu
suggested.
Eg:
If your table was like
create table table1 (col1 integer not null)
then you can use

Alter table table1 modify col1 integer null
 

to remove the constraint.

Hope it helped
Cheers

[chandu031]

Hi,

If you just want to drop the not null constraint then there is a better way of doing it.

Just use the ALTER TABLE command:

 
ALTER TABLE <table_name> MODIFY <column> <datatype> NULL
 
 

Of course this does not work the other way round i.e from NULL to NOT NULL
if there are already NULLs in your table. It will work if the field doesn't have NULLs