CODEWITHSUNDEEP
sqldatabaseoracle-databaseconstraints
John Manak
John Manak

Reputation: 13558

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

Upvotes: 13

Views: 42386

Answers (4)

Ganna Anil Kumar
Ganna Anil Kumar

Reputation: 11

example: we can drop constraints in MySQL by creating constraints to the variables like this way.

create table sample(id int, name varchar(30), marks int, constraint uid unique(id), constraint un unique(name));
alter table sample drop constraint uid, drop constraint un;

Upvotes: 1

user16844392
user16844392

Reputation: 1

Yes, we can drop multiple at once:

ALTER TABLE TABLE NAME
DROP CONSTRAINTS CONSTRAINT VALUE
DROP CONSTRAINTS CONSTRAINT VALUE;

Upvotes: 0

Jordi M.
Jordi M.

Reputation: 69

There is an alternative form to drop constraints related to a column in a table, also dropping the column with CASCADE:

ALTER TABLE table1 DROP (columnName) CASCADE CONSTRAINTS;

It is tested on Oracle 11g

Upvotes: 1

Sodved
Sodved

Reputation: 8588

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.

Upvotes: 28

Related Questions