469,282 Members | 2,175 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,282 developers. It's quick & easy.

How to drop Not Null constraint (system generated)

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
3 69218
chandu031
78 Expert
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 :

Expand|Select|Wrap|Line Numbers
  1.  
  2. SELECT CONSTRAINT_NAME FROM ALL_CONS_COLUMNS WHERE OWNER = <user_name> AND TABLE_NAME = <table_name>
  3. AND COLUMN_NAME = <column_name> 
  4.  
May 21 '07 #2
frozenmist
179 Expert 100+
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
Expand|Select|Wrap|Line Numbers
  1. Alter table table1 modify col1 integer null
  2.  
to remove the constraint.

Hope it helped
Cheers
May 21 '07 #3
chandu031
78 Expert
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:

Expand|Select|Wrap|Line Numbers
  1.  
  2. ALTER TABLE <table_name> MODIFY <column> <datatype> NULL
  3.  
  4.  
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

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

4 posts views Thread by Kevin Haugen | last post: by
4 posts views Thread by Ken D. | last post: by
5 posts views Thread by annecarterfredi | last post: by
1 post views Thread by femina | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.