OraFAQ Forum: SQL & PL/SQL » To drop all constraints of a table

Home » SQL & PL/SQL » SQL & PL/SQL » To drop all constraints of a table

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

To drop all constraints of a table [message #255117]

Mon, 30 July 2007 12:27

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

Report message to a moderator

Re: To drop all constraints of a table [message #255119 is a reply to message #255117]

Mon, 30 July 2007 12:32

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

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