Geek Answers Handbook

Drop index or restriction without knowing its name for Oracle

In the oracle database I have a foreign key without knowing its name, just column_name and directory_name directory. I want to write a sql script that should remove 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 has completed an “anonymous block”, so it was successful, but when I add part of the drop:

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 the following:

Error Report: ORA-06550: row 9, column 5: PLS-00103: character "ALTER", expecting one of the following:

begin case declare exit goto if loop mod null pragma raise , < fetch lock insert open rollback savepoint set sql commit forall merge pipe 06550. 00000 - "% s, % s:\n% s" * : PL/SQL. * :

- , ?

:

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;

, "alter table"

:

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');

:

: SQL: ORA-02250: 02250. 00000 - " " * : . * : .

SQL- (MS SQL) . @ , . , ...

+5
2

, , DDL PL/SQL; , EXECUTE IMMEDIATE:

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

, - , , fName -, , -.

, :

if (fName != '') THEN

/, Oracle '' NULL.

IF fName IS NOT NULL THEN

.

+6

Here's how to remove all the restrictions typed by "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;
+2
source

More articles:


All Articles