View Poll Results: How to drop constraint whitout knowing his name?
- 0. You may not vote on this poll
Other way to drop constraint
Other way to drop constraints
11-18-04, 11:44 #1Registered User
- Join Date
- Nov 2004
Unanswered: Drop constraint like "alter table T drop constrain 'referencedTable','Tcolum' "
Hi, I have many databases in ORACLE and I have to drop constrain in some tables in all dadabases. Problem is, that in databases are same names of tables and colums, but constraints has diferent name. How can I drop with the same syntax in all databases the constraint between same tablename and columname?
I HAVE TO USE THE SAME SYNTAX, THATS THE PROBLEM. I'm looking for
somethink like that:
alter table TABLE_S drop constrain 'referencedTable','TABLE_Scolum'
Of course, it isn't working, this syntax is BAD, psl help me with the right syntax or solution.
Thank you very much.
11-18-04, 12:07 #2Moderator.
Provided Answers: 1
- Join Date
- Sep 2002
Something like this:
declare l_table_name varchar2(30) := 'T1'; l_column_name varchar2(30) := 'ID'; begin for r in (select constraint_name from user_cons_columns where table_name=l_table_name and column_name=l_column_name) loop execute immediate 'alter table ' || l_table_name || ' drop constraint ' || r.constraint_name; end loop; end; /Tony Andrews