John Manak John Manak - 7 months ago 28
SQL Question

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
);


it doesn't work and I need to do:

ALTER TABLE tblApplication DROP CONSTRAINT constraint1_name;
ALTER TABLE tblApplication DROP CONSTRAINT constraint2_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,
};

Answer

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.