Home » SQL & PL/SQL » SQL & PL/SQL » To drop all constraints of a table
To drop all constraints of a table [message #255117] Mon, 30 July 2007 12:27 Go to next message
spsbombay
Messages: 29
Registered: June 2007
Junior Member

Hi,

1 more query.
Can we drop all constraints of a table simultenously? if yes how?

Regards,
Prashant
Re: To drop all constraints of a table [message #255119 is a reply to message #255117] Mon, 30 July 2007 12:32 Go to previous messageGo to next message
DreamzZ
Messages: 1666
Registered: May 2007
Location: Dreamzland
Senior Member
some thing like this.


SQL>  select 'alter table '||owner||'.'||table_name||' drop constraint '||CONSTRAINT_NAME||' ;' from dba_constraints
  2   where owner='ORACLE' and table_name ='TS_STATS';

'ALTERTABLE'||OWNER||'.'||TABLE_NAME||'DROPCONSTRAINT'||CONSTRAINT_NAME||';'
--------------------------------------------------------------------------------
alter table ORACLE.TS_STATS drop constraint NN_TS_STATS_ELAPSED ;
alter table ORACLE.TS_STATS drop constraint NN_TS_STATS_TRANSACTION ;
alter table ORACLE.TS_STATS drop constraint NN_TS_STATS_CLIENT ;
alter table ORACLE.TS_STATS drop constraint NN_TS_STATS_SERVICE ;
alter table ORACLE.TS_STATS drop constraint NN_TS_STATS_SERVER ;
alter table ORACLE.TS_STATS drop constraint NN_TS_STATS_REQUEST ;
alter table ORACLE.TS_STATS drop constraint SYS_C006181 ;

7 rows selected.


[Updated on: Mon, 30 July 2007 12:33]

Report message to a moderator

Re: To drop all constraints of a table [message #255122 is a reply to message #255117] Mon, 30 July 2007 12:41 Go to previous messageGo to next message
MarcL
Messages: 455
Registered: November 2006
Location: Connecticut, USA
Senior Member
No, but you can write code which will write a script that drops each constraint.

Example:
SQL> spool drop_const.sql
Started spooling to C:\Program Files\PLSQL Developer\drop_const.sql

SQL> select 'ALTER TABLE TABLE_NAME DROP CONSTRAINT ' || constraint_name from user_constraints
  2  where table_name = 'SFWID_ORDER_NODE';

'ALTERTABLETABLE_NAMEDROPCONST
---------------------------------------------------------------------

ALTER TABLE TABLE_NAME DROP CONSTRAINT SYS_C006621
ALTER TABLE TABLE_NAME DROP CONSTRAINT SYS_C006622
ALTER TABLE TABLE_NAME DROP CONSTRAINT SYS_C006623
ALTER TABLE TABLE_NAME DROP CONSTRAINT SYS_C006624
ALTER TABLE TABLE_NAME DROP CONSTRAINT SYS_C006625
ALTER TABLE TABLE_NAME DROP CONSTRAINT SYS_C006626
ALTER TABLE TABLE_NAME DROP CONSTRAINT SYS_C006627
ALTER TABLE TABLE_NAME DROP CONSTRAINT SYS_C006628
ALTER TABLE TABLE_NAME DROP CONSTRAINT SYS_C006629
ALTER TABLE TABLE_NAME DROP CONSTRAINT SFWID_ORDER_NODE_PK

13 rows selected

SQL> spool off;


You probably want to do set head off as well.

(Sorry Dreamzz.. didn't see yours, I was cuttin and pasting)

[Updated on: Mon, 30 July 2007 12:42]

Report message to a moderator

Re: To drop all constraints of a table [message #255123 is a reply to message #255122] Mon, 30 July 2007 12:55 Go to previous messageGo to next message
DreamzZ
Messages: 1666
Registered: May 2007
Location: Dreamzland
Senior Member
Its ok Man No problama. Wink
Re: To drop all constraints of a table [message #255131 is a reply to message #255122] Mon, 30 July 2007 13:32 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
OK Boys, you both forgot CASCADE keyword otherwise you'll likely have problems with foreign keys. Wink

Regards
Michel

[Updated on: Mon, 30 July 2007 13:35]

Report message to a moderator

Re: To drop all constraints of a table [message #255138 is a reply to message #255131] Mon, 30 July 2007 14:07 Go to previous message
DreamzZ
Messages: 1666
Registered: May 2007
Location: Dreamzland
Senior Member
Good point Wink
Previous Topic: How to manipulate data at bit level?
Next Topic: Extract data in excel/csv and enail automatically
Goto Forum:
  


Current Time: Fri Dec 09 21:42:15 CST 2016

Total time taken to generate the page: 0.08158 seconds