如何从Oracle数据库中删除基于列的约束？ [英] How to remove constraint based on columns from oracle database?

问题描述

有没有一种方法可以根据列名删除约束（唯一索引）？

Is there is a way to remove a constraint (unique index) based on columns name?

我想做的就是删除列名所在的约束是name和name_type。

What I would like to do is to remove a constraint where columna name is name, and name_type.

ALTER TABLE MY_TABLE DROP CONSTRAINT NAME_OF_CONSTRAINT;

我没有名字，所以我想用这种方式...

I don't have a name so I would like to do it this way...

ALTER TABLE MY_TABLE DROP CONSTRAINT ** WHERE COLUMN = col1 AND column = col2 **

任何在约束上执行类似操作的语法。

Any syntax to do something like this on a constraint.

推荐答案

我认为仅凭一条语句就不可能做到这一点，但事实证明确实如此，如在文档的示例中：

I didn't think this was possible with a single statement, but it turns out it is, as shown in the examples in the documentation:

ALTER TABLE MY_TABLE DROP UNIQUE(col1, col2);

一个完整的示例：
ALTER TABLE MY_TABLE ADD UNIQUE（col1，col2）;

A complete example: ALTER TABLE MY_TABLE ADD UNIQUE (col1, col2);

Table my_table altered.

SELECT CONSTRAINT_NAME, INDEX_NAME
FROM USER_CONSTRAINTS
WHERE TABLE_NAME = 'MY_TABLE';

CONSTRAINT_NAME                INDEX_NAME                    
------------------------------ ------------------------------
SYS_C0092455                   SYS_C0092455                   

ALTER TABLE MY_TABLE DROP UNIQUE(col1, col2);

Table my_table altered.

SELECT CONSTRAINT_NAME, INDEX_NAME
FROM USER_CONSTRAINTS
WHERE TABLE_NAME = 'MY_TABLE';

no rows selected

另一种方法是查询 USER_CONSTRAINTS 和 USER_CONS_COLUMNS 视图查找匹配的约束名称（大概是系统生成的，或者您已经知道了），然后使用该名称。如果需要以脚本形式执行此操作，则可以在PL / SQL块中进行查询，然后将找到的约束名称插入动态的 ALTER TABLE 语句中。

An alternative approach is to query the USER_CONSTRAINTS and USER_CONS_COLUMNS views to find the matching constraint name - presumably system-generated or you would already know it - and then use that name. If you need to do this as a script then you could query in a PL/SQL block, and plug the found constraint name into a dynamic ALTER TABLE statement.

这篇关于如何从Oracle数据库中删除基于列的约束？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！