CODEWITHSUNDEEP
oracle-databaseconstraints
im8bit
im8bit

Reputation: 577

Is there a way in oracle to disable/enable an unnamed constraint?

I want to disable NOT NULL constraints into a table to insert data for test but I can't find a way to disable unnamed constraints.

I found enough info to disable named constraints, but I couldn't find a example to disable unnamed NOT NULL constraint.

I would like to implement this without querying the data dictionary, but... I'm willing to do that if its the only way. But I would like to use a clean ALTER TABLE DDL.

Upvotes: 3

Views: 3414

Answers (2)

Gary Myers
Gary Myers

Reputation: 35401

You can also just alter the column as follows

create table test_null (col_n number not null);
alter table test_null modify col_n number null;

Upvotes: 5

Matthew Watson
Matthew Watson

Reputation: 14233

You will need to query the data dictionary, something like this will disable all constraints on the table. Be aware though, that this will disable the constraint system wide, not just for your session.. Perhaps what you really want is to defer the constraint?

drop table testxx

 drop table testxx succeeded.
create table testxx ( id number not null ) 

create table succeeded.
select status from user_constraints where table_name = 'TESTXX'

STATUS   
-------- 
ENABLED  

1 rows selected

begin
 for cnames in ( select table_name,constraint_name from user_constraints where table_name = 'TESTXX' ) loop
    execute immediate 'alter table ' || cnames.table_name || ' disable constraint ' || cnames.constraint_name;
  end loop;
end;

anonymous block completed
select status from user_constraints where table_name = 'TESTXX'

STATUS   
-------- 
DISABLED 

1 rows selected

Upvotes: 5

Related Questions