如何可以删除“不为空”约束在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的解决方案。

在我自己的数据库上，删除这个的SQL将是：

  alter table MYTABLE drop constraint SYS_C0044566 
  

我查看 all_constraints view：

  select * from all_constraints where table_name ='MYTABLE'
  pre> 
 
 
但我不知道如何使用 SEARCH_CONDITION 的 LONG 数据类型或如何最好地动态删除查找的约束，即使我知道它的名称。
 
 
 
那么，如何创建一个更改脚本
 
 
 
 
 
 
编辑：
可以删除这个约束，而不是它的名字是什么？ @ Allan的答案是一个好的，但我担心（在我缺乏Oracle专业知识），它可能不是普遍真的，可能有一个系统生成的名称的任何约束将与它相关联的一种方式来删除约束没有不得不知道它的名字。是否真的，总是有一种方法，以避免在逻辑删除该约束时知道系统命名的约束的名称？
解决方案
 
  alter table MYTABLE modify（MYCOLUMN null）; 
 
在Oracle中，如果为列指定了not null，则不会自动创建空约束。同样，当列更改为允许空值时，它们会自动删除。
 
 
 
 澄清修订的问题：此解决方案仅适用于not null列。如果在列定义中指定主键或检查约束而不对其进行命名，则最终将为约束（以及主键的索引）生成一个系统生成的名称。在这些情况下，您需要知道名称才能删除它。最好的建议是通过确保为除非空之外的所有约束指定名称来避免该情况。如果你发现自己处于需要一般删除这些约束的情况，你可能需要求助于PL / SQL和数据定义表。
 
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.

On my own database, the SQL to drop this would be:
alter table MYTABLE drop constraint SYS_C0044566
I can see the constraint when I query the all_constraints view:
select * from all_constraints where table_name = 'MYTABLE'
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?



EDIT:
@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);
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.

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屋！