当我不知道约束的名称时，如何在 Oracle 中删除“非空”约束？

How can I drop a "not null" constraint in Oracle when I don't know the name of the constraint?

提问by Chris Farmer

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.

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

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

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

alter table MYTABLE drop constraint SYS_C0044566

I can see the constraint when I query the all_constraintsview:

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

select * from all_constraints where table_name = 'MYTABLE'

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

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

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?

编辑：@Allan 的回答是一个很好的答案，但我担心（由于我缺乏 Oracle 专业知识），任何可能具有系统生成名称的约束都将与它相关联的一种方法来删除约束而不必知道其名称。在逻辑上删除该约束时，总是有一种方法可以避免必须知道系统命名约束的名称吗？

回答by Allan

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.

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

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.

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

回答by vasanth

Try:

尝试：

alter table <your table> modify <column name> null;

回答by Mary C

Just remember, if the field you want to make nullable is part of a primary key, you can't. Primary Keys cannot have null fields.

请记住，如果您要设置为可为空的字段是主键的一部分，则不能。主键不能有空字段。

回答by FrostyDog

To discover any constraints used, use the code below:

要发现使用的任何约束，请使用以下代码：

-- Set the long data type for display purposes to 500000.

SET LONG 500000

-- Define a session scope variable.

VARIABLE output CLOB

-- Query the table definition through the <code>DBMS_METADATA</code> package.

SELECT dbms_metadata.get_ddl('TABLE','[Table Described]') INTO :output FROM dual;

This essentially shows a create statement for how the referenced table is made. By knowing how the table is created, you can see all of the table constraints.

这实质上显示了如何创建引用表的 create 语句。通过了解表的创建方式，您可以查看所有表约束。

Answer taken from Michael McLaughlin's blog: http://michaelmclaughlin.info/db1/lesson-5-querying-data/lab-5-querying-data/From his Database Design I class.

答案取自 Michael McLaughlin 的博客：http: //michaelmclaughlin.info/db1/lesson-5-querying-data/lab-5-querying-data/来自他的 Database Design I 课程。

回答by Francisco

Something like that happened to me when I made copies of structures to temporary tables, so I removed the not null.

当我将结构复制到临时表时，类似的事情发生在我身上，所以我删除了 not null。

DECLARE
   CURSOR cur_temp_not_null IS
        SELECT table_name, constraint_name  FROM all_constraints WHERE table_name LIKE 'TEMP_%' AND  owner='myUSUARIO';

   V_sql VARCHAR2(200); 

BEGIN
  FOR c_not_null IN cur_temp_not_null
   LOOP
     v_sql :='ALTER TABLE ' || c_not_null.table_name || ' DROP CONSTRAINT '|| c_not_null.constraint_name;
     EXECUTE IMMEDIATE  v_sql;     
  END LOOP;
END;

回答by Lukasz Ciesluk

If constraint on column STATUS was created without a name during creating a table, Oracle will assign a random name for it. Unfortunately, we cannot modify the constraint directly.

如果在创建表期间创建了列 STATUS 的约束而没有名称，则 Oracle 将为它分配一个随机名称。不幸的是，我们不能直接修改约束。

Steps involved of dropping unnamed constraint linked to column STATUS

删除链接到列 STATUS 的未命名约束所涉及的步骤

1. Duplicate STATUS field into a new field STATUS2
2. Define CHECK constraints on STATUS2
3. Migrate data from STATUS into STATUS2
4. Drop STATUS column

5. Rename STATUS2 to STATUS

   ALTER TABLE MY_TABLE ADD STATUS2 NVARCHAR2(10) DEFAULT 'OPEN'; ALTER TABLE MY_TABLE ADD CONSTRAINT MY_TABLE_CHECK_STATUS CHECK (STATUS2 IN ('OPEN', 'CLOSED')); UPDATE MY_TABLE SET STATUS2 = STATUS; ALTER TABLE MY_TABLE DROP COLUMN STATUS; ALTER TABLE MY_TABLE RENAME COLUMN STATUS2 TO STATUS;

1. 将 STATUS 字段复制到新字段 STATUS2
2. 在 STATUS2 上定义 CHECK 约束
3. 将数据从 STATUS 迁移到 STATUS2
4. 删除 STATUS 列

5. 将 STATUS2 重命名为 STATUS

   ALTER TABLE MY_TABLE ADD STATUS2 NVARCHAR2(10) DEFAULT 'OPEN'; ALTER TABLE MY_TABLE ADD CONSTRAINT MY_TABLE_CHECK_STATUS CHECK (STATUS2 IN ('OPEN', 'CLOSED')); UPDATE MY_TABLE SET STATUS2 = STATUS; ALTER TABLE MY_TABLE DROP COLUMN STATUS; ALTER TABLE MY_TABLE RENAME COLUMN STATUS2 TO STATUS;

回答by CaduMaciel

I was facing the same problem trying to get around a custom check constraint that I needed to updated to allow different values. Problem is that ALL_CONSTRAINTS does't have a way to tell which column the constraint(s) are applied to. The way I managed to do it is by querying ALL_CONS_COLUMNS instead, then dropping each of the constraints by their name and recreate it.

我在试图绕过我需要更新以允许不同值的自定义检查约束时遇到了同样的问题。问题是 ALL_CONSTRAINTS 无法判断约束应用于哪一列。我设法做到这一点的方法是查询 ALL_CONS_COLUMNS，然后按名称删除每个约束并重新创建它。

select constraint_name from all_cons_columns where table_name = [TABLE_NAME] and column_name = [COLUMN_NAME];

从 all_cons_columns 中选择约束名称，其中 table_name = [TABLE_NAME] 和 column_name = [COLUMN_NAME]；