View Poll Results: How to drop constraint whitout knowing his name?

Voters
0. You may not vote on this poll
  • Other way to drop constraint

    0 0%
  • Other way to drop constraints

    0 0%
Multiple Choice Poll.
Results 1 to 2 of 2
  1. #1
    Join Date
    Nov 2004
    Posts
    1

    Question 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.

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Something like this:
    Code:
    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;
    /
    I suggest you try it with DBMS_OUTPUT.PUT_LINE instead of EXECUTE IMMEDIATE until you are sure it is correct!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •