Home » SQL & PL/SQL » SQL & PL/SQL » Delete all constraints related to a table
Delete all constraints related to a table [message #294318] Thu, 17 January 2008 03:56 Go to next message
kecd_deepak
Messages: 52
Registered: December 2007
Member
How can I Delete all constraints related to a table in a singe Querry OR Script.


Why we can not modify structure of a Constraints.
Re: Delete all constraints related to a table [message #294328 is a reply to message #294318] Thu, 17 January 2008 04:16 Go to previous messageGo to next message
Michel Cadot
Messages: 64107
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Query user_constraints and generates the appropriate statement depending on the constraint type.

Regards
Michel
Re: Delete all constraints related to a table [message #294660 is a reply to message #294318] Fri, 18 January 2008 06:32 Go to previous messageGo to next message
ramanajv1968
Messages: 168
Registered: December 2005
Location: HYDERABAD
Senior Member
Below is the one way(not tested query):

If query the user_constraits table

SQL> select CONSTRAINT_NAME,CONSTRAINT_TYPE,TABLE_NAME from user_constraints where table_name ='TEST_EMP';

CONSTRAINT_NAME                C TABLE_NAME
------------------------------ - ------------------------------
SYS_C009304                    C TEST_EMP
SYS_C009305                    C TEST_EMP
EMP_PK                         P TEST_EMP


Now we are sure that the above query will fetch all the constraints on the table.
then by using the below single statement in you are script you can delee all the constarints on that table(not really test)Sad

delete from user_constraints 
where CONSTRAINT_NAME in (select CONSTRAINT_NAME from user_constraints where table_name ='TEST_EMP') ;

commit; 

[Updated on: Fri, 18 January 2008 07:24] by Moderator

Report message to a moderator

Re: Delete all constraints related to a table [message #294679 is a reply to message #294660] Fri, 18 January 2008 07:28 Go to previous message
Michel Cadot
Messages: 64107
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
First, don't put the whole post inside code tags, just the code part. Also make sure that lines of code do not exceed 80 characters when you format. Use the "Preview Message" button to verify.

Then, trying to delete a catalog view is... wrong. You delete a constraint using "alter table"

In the end, why something so complex than
delete t where id in (select id from t where ...)
just use:
delete t where ...

Regards
Michel
Previous Topic: Column Altered Backing Out
Next Topic: ORA-01917: user or role '' does not exist
Goto Forum:
  


Current Time: Mon Dec 05 04:39:47 CST 2016

Total time taken to generate the page: 0.29449 seconds