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
);
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||'.
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.
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With