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

May 21 '07 #1

chandu031

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 :

May 21 '07 #2

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
to remove the constraint.

Hope it helped
Cheers

May 21 '07 #3

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:

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

May 21 '07 #4