开发者

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


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.


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


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;


Yes, we can drop multiple at once:

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

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜