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

score 7 · Accepted Answer · answered Jan 25 '12 at 18:56

7

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.

answered Jan 25 '12 at 18:56

ruakh

175,680
26
273
307

Hey just an additional question about this: With this select statement theoretical I would get more than one constraint name if a column C1 of table A1 references to C2 of table A2 and C1 references to C3 of table A3, so I would get the names for C1->C2 and C1->C3, so the question is where can I join the info to which column I am referencing??? – radio Jan 26 '12 at 06:52
@radio: On `all_constraints`, `(r_owner, r_constraint_name)` is effectively a foreign key; you can join back to `all_constraints` to get information about the referenced column(s). (Inside Oracle, a foreign key is implemented as a foreign key to *another constraint*. This makes sense when you consider what the DBMS needs to do to enforce a foreign-key constraint.) – ruakh Jan 26 '12 at 15:48
@radio: By the way, your "theoretical" concern worries me a bit, because there are other, bigger problems with your bit of code -- for example, you use `all_constraints` and `all_cons_columns` without checking `owner`, and you don't check to make sure that `'MY_COLUMN_NAME'` is the only column in the foreign key. So your bit of code is not general-purpose. I was assuming that you planned to use it in a specific circumstance where you had a lot of other information, but were merely missing the constraint-name for some reason. If you need general-purpose code, you have a lot of things to fix. – ruakh Jan 26 '12 at 15:51
Well, you have right, this is not a general purpose one. I actually know exactly how my source table should looks like and I don't expect any other owners that have the same table name/columns, so I don't have to worry about other owners etc., right? I just need to do some manual modification to bring my source table to a specific destination state, that's all. – radio Jan 27 '12 at 06:50
how can I check whether 'MY_COLUMN_NAME' is the only column in the foreign key or index (now I need this too), additional sys table joins?. I've added the owner already – radio Aug 02 '12 at 13:00
@radio: You would check `all_cons_columns` (or `all_ind_columns`, for an index) to see if there are any other columns belonging to the same constraint (or index). – ruakh Aug 02 '12 at 13:43
but I have to differentiate between an index specified for multiple columns and multiple indexes specified for a single column. E.g.: index a on column a, b on b, c on c, would be shown in the all_ind_columns as 3 rows for the different columns, and when index x on columns (a, b, c) would be the same in the all_ind_column, except that the index name would be different – radio Aug 02 '12 at 13:47
@radio: Yes, exactly. That's why I said that you need to check to see if there are any other columns belonging to the *same* index. – ruakh Aug 02 '12 at 13:55
well, I just didn't want to bring more complexity into the structure, now somehow I have to adapt the select statement in order to give me this information too, cause all I want to do is to add an index on a column, but if the index is already there, just rename it, if not create it. So what I tried is to first get the name of the index for the specific column -> if same as mine, then do nothing, if other then mine, then rename, if no index, then create new one with my name – radio Aug 02 '12 at 14:12
@radio: Indices have an order. If there's an index on `(a, b, c)`, then there may be a reason to create an index on `b`, but there is certainly no reason to create an index on just `a` (I mean, unless the index on `(a, b, c)` is disabled, or you intend to drop it, or whatnot). So, the best way to see if there's an index on a given column is to run `SELECT 1 FROM all_ind_columns WHERE table_owner = ... AND table_name = ... AND column_name = ... AND column_position = 1`. – ruakh Aug 02 '12 at 14:49
no matter if it would not make sense, I want to run a script on a table, for which I don't know if someone has specified multiple indexes or not (maybe he has messed the order too), so I cannot only rely on the order, I tried something like: select ... where ... AND NOT EXISTS (select ... same index_name for other colums), the NOT EXISTS is for SQL SERVER, for Oracle I have to see what I can use, but still this approach doesn't work for me (maybe some semantic/syntax problems) – radio Aug 02 '12 at 15:08
@radio: This is too big for comments, and also, it's been more than six months. I think you should post a new question. – ruakh Aug 02 '12 at 15:12

score 4 · Answer 2 · edited Jan 22 '15 at 14:08

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;

drop index or constraint without knowing its name for Oracle

2 Answers2