Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to drop more than one constraint at once (Oracle, SQL)

I'm changing constraints in my database and I need to drop some of them. I know that for a single constraint, the command is following:

ALTER TABLE tblApplication DROP CONSTRAINT constraint1_name;

However, when I try

ALTER TABLE tblApplication DROP (
  CONSTRAINT constraint1_name,
  CONSTRAINT constraint2_name,
  CONSTRAINT constraint3_name
);

it doesn't work and I need to do:

ALTER TABLE tblApplication DROP CONSTRAINT constraint1_name;
ALTER TABLE tblApplication DROP CONSTRAINT constraint2_name;
ALTER TABLE tblApplication DROP CONSTRAINT constraint3_name;

Is there a way to remove more than one constraint in a single command? I'd like to avoid repeating ALTER TABLE tblApplication, just like with the ADD command:

ALTER TABLE tblApplication ADD (
  CONSTRAINT contraint1_name FOREIGN KEY ... ENABLE,
  CONSTRAINT contraint2_name FOREIGN KEY ... ENABLE,
  CONSTRAINT contraint3_name FOREIGN KEY ... ENABLE
);
like image 462
John Manak Avatar asked May 02 '11 15:05

John Manak


People also ask

How can I drop all constraints on a table in Oracle?

Drop all the constraint in Oracle begin for r in ( select table_name, constraint_name from user_constraints where table_name='&1' ) loop execute immediate 'alter table '|| r. table_name ||' drop constraint '|| r. constraint_name; end loop; end; OR select 'alter table '||owner||'.

What is the difference between disable and drop constraint?

If you're doing data loads or other maintenance, it makes sense to disable rather than drop a constraint. The idea is, if you know you're going to want to get that constraint back when you're done w/ the data load or maintenance, then disable it. When you're done, you can simply enable it.


1 Answers

Yes you can. You just need to repeat 'drop constraint' per constraint. e.g.

alter table t1
drop constraint fk1
drop constraint fk2
/

Edit: I tested this against Oracle 11, and it worked fine. Don't know about older versions.

like image 142
Sodved Avatar answered Oct 30 '22 09:10

Sodved