我怎样才能删除“非空"?当我不知道约束的名称时，Oracle 中的约束? [英] How can I drop a "not null" constraint in Oracle when I don't know the name of the constraint?

问题描述

我有一个对字段有 NOT NULL 约束的数据库，我想删除此约束.复杂的因素是该约束具有系统定义的名称，并且该约束的名称在生产服务器、集成服务器和各种开发人员数据库之间有所不同.我们当前的流程是检查更改脚本，一个自动化任务通过 sqlplus 对目标数据库执行适当的查询，所以我更喜欢一个可以直接发送到 sqlplus 的解决方案.

I have a database which has a NOT NULL constraint on a field, and I want to remove this constraint. The complicating factor is that this constraint has a system-defined name, and that constraint's name differs between the production server, integration server, and the various developer databases. Our current process is to check in change scripts, and an automated task executes the appropriate queries through sqlplus against the target database, so I'd prefer a solution that could just be sent straight into sqlplus.

在我自己的数据库上，删除它的 SQL 是:

On my own database, the SQL to drop this would be:

alter table MYTABLE drop constraint SYS_C0044566

当我查询 all_constraints 视图时可以看到约束:

I can see the constraint when I query the all_constraints view:

select * from all_constraints where table_name = 'MYTABLE'

但我不确定如何使用 SEARCH_CONDITION 的 LONG 数据类型或如何最好地动态删除查找的约束，即使我知道它的名称.

but I am not sure how to work with the SEARCH_CONDITION's LONG data type or how best to dynamically delete the looked-up constraint even after I know its name.

那么，我如何创建一个更改脚本，该脚本可以根据它的内容而不是它的名称来删除此约束?

So, how can I create a change script that can drop this constraint based on what it is, rather than what its name is?

@Allan 的回答很好，但我担心(由于我缺乏 Oracle 专业知识)可能并非普遍正确的是，任何可能具有系统生成名称的约束都将与它相关联，这是一种无需删除约束的方法必须知道它的名字.在逻辑上删除该约束时，总是有一种方法可以避免必须知道系统命名的约束的名称吗?

@Allan's answer is a good one, but I am concerned (in my lack of Oracle expertise) that it may not be universally true that any constraint that might have a system-generated name will have associated with it a way to remove the constraint without having to know its name. Is it true that there will always be a way to avoid having to know a system-named constraint's name when logically dropping that constraint?

推荐答案

alter table MYTABLE modify (MYCOLUMN null);

在 Oracle 中，当为列指定 not null 时，会自动创建 not null 约束.同样，当列更改为允许空值时，它们会自动删除.

In Oracle, not null constraints are created automatically when not null is specified for a column. Likewise, they are dropped automatically when the column is changed to allow nulls.

澄清修改后的问题:此解决方案仅适用于为非空"列创建的约束.如果您在列定义中指定主键"或检查约束而未命名，则最终会得到系统生成的约束名称(以及主键的索引).在这些情况下，您需要知道名称才能删除它.最好的建议是通过确保为除not null"以外的所有约束指定一个名称来避免这种情况.如果您发现自己需要一般地删除这些约束之一，您可能需要求助于 PL/SQL 和数据定义表.

Clarifying the revised question: This solution only applies to constraints created for "not null" columns. If you specify "Primary Key" or a check constraint in the column definition without naming it, you'll end up with a system-generated name for the constraint (and the index, for the primary key). In those cases, you'd need to know the name to drop it. The best advice there is to avoid the scenario by making sure you specify a name for all constraints other than "not null". If you find yourself in the situation where you need to drop one of these constraints generically, you'll probably need to resort to PL/SQL and the data-definition tables.

这篇关于我怎样才能删除“非空"?当我不知道约束的名称时，Oracle 中的约束?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！