drop index or constraint without knowing its name for Oracle

Question

On a oracle database I have a foreign key, without knowing its name, just the column_name and the reference_column_name. I want to write a sql script which should drop this foreign key if it exists, so this is the code I use:

declare
 fName varchar2(255 char);
begin
 SELECT x.constraint_name into fName FROM all_constraints x
 JOIN all_cons_columns c ON
 c.table_name = x.table_name AND c.constraint_name = x.constraint_name
 WHERE x.table_name = 'MY_TABLE_NAME' AND x.constraint_type = 'R' AND c.column_name ='MY_COLUMN_NAME';
end;

The output of this script is "anonymous block completed", so it was successful, but when I add the drop part:

declare
 fName varchar2(255 char);
begin
 SELECT x.constraint_name into fName FROM all_constraints x
 JOIN all_cons_columns c ON
 c.table_name = x.table_name AND c.constraint_name = x.constraint_name
 WHERE x.table_name = 'MY_TABLE_NAME' AND x.constraint_type = 'R' AND c.column_name ='MY_COLUMN_NAME';
 if (fName != '') THEN
  alter table MY_TABLE_NAME drop constraint fName;
 end if;
end;

Then I get this one:

Error report: ORA-06550: line 9, column 5: PLS-00103: Encountered the symbol "ALTER" when expecting one of the following:

begin case declare exit for goto if loop mod null pragma raise return select update while with << close current delete fetch lock insert open rollback savepoint set sql execute commit forall merge pipe 06550. 00000 - "line %s, column %s:\n%s" *Cause: Usually a PL/SQL compilation error. *Action:

So can anyone tell me what is the problem here?

I also tried to put everything into a function:

declare
  function getFName return varchar2 is
    fName varchar2(255 char);
  begin
   SELECT x.constraint_name into fName FROM all_constraints x
   JOIN all_cons_columns c ON
   c.table_name = x.table_name AND c.constraint_name = x.constraint_name
   WHERE x.table_name = 'MY_TABLE_NAME' AND x.constraint_type = 'R' AND c.column_name ='MY_COLUMN_NAME';

   return fName;
  end;
begin
  if getFName() != '' then
   alter table all_events drop constraint getFName(); 
  end if;
end; 

The result was the same error caused by the statement "alter table"

This one also did not help:

alter table all_events drop constraint
   (SELECT x.constraint_name into fName FROM all_constraints x
   JOIN all_cons_columns c ON
   c.table_name = x.table_name AND c.constraint_name = x.constraint_name
   WHERE x.table_name = 'MY_TABLE_NAME' AND x.constraint_type = 'R' AND c.column_name ='MY_COLUMN_NAME');

The output was:

Error report: SQL Error: ORA-02250: missing or invalid constraint name 02250. 00000 - "missing or invalid constraint name" *Cause: The constraint name is missing or invalid. *Action: Specify a valid identifier name for the constraint name.

For a sql server (MS SQL) there is so easy to do this. Just declaring a variable with @ and the set it, after that just use it. On oracle I don't have any clue what it isn't working...

Answer 1

Your original version is mostly fine, except that you can't directly execute DDL in a PL/SQL block; rather, you have to wrap it in an EXECUTE IMMEDIATE:

execute immediate 'alter table MY_TABLE_NAME drop constraint "' || fName || '"';

This would be true even if the constraint-name were known at compile-time, but it's doubly true in your case, since fName isn't the constraint-name, but rather, a variable containing the constraint-name.

Also, this:

if (fName != '') THEN

is not valid/meaningful, since in Oracle '' means NULL. You should write

IF fName IS NOT NULL THEN

instead.

Answer 2

This is how to drop all constraints typed "R" for a column:

begin
    FOR rec IN (SELECT x.constraint_name fName FROM all_constraints x
        JOIN all_cons_columns c ON
        c.table_name = x.table_name AND c.constraint_name = x.constraint_name
        WHERE x.table_name = 'MY_TABLE_NAME' AND x.constraint_type = 'R' AND c.column_name ='MY_COLUMN_NAME')
    LOOP
        EXECUTE IMMEDIATE 'ALTER TABLE MY_TABLE_NAME DROP CONSTRAINT "' || rec.fName || '"';
    END LOOP;
end;