oracle根据索引名删除索引,sql – 删除索引或约束而不知道Oracle的名称

在oracle数据库上,我有一个外键,不知道它的名字,只有column_name和reference_column_name.

我想写一个sql脚本,如果它存在,应该删除这个外键,所以这是我使用的代码：

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;

这个脚本的输出是“匿名块完成”,所以它很成功,但是当我添加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;

然后我得到这个：

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:

那么有谁能告诉我这里的问题是什么？

我还试图把所有东西都放到一个函数中：

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

输出是：

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.

对于SQL Server(MS SQL),这很容易做到这一点.只需使用@声明一个变量并设置它,然后使用它.在oracle上我没有任何线索它不起作用……