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

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

在我自己的数据库中，删除这个命令的 SQL 应该是:

alter table MYTABLE drop constraint SYS_C0044566

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

select * from all_constraints where table_name = 'MYTABLE'

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

那么，我如何创建一个更改脚本，它可以根据约束是什么而不是它的名称来删除这个约束呢？

编辑: @ Allan 的回答很好，但是我担心(因为我缺乏 Oracle 的专业知识) ，任何可能有系统生成名称的约束都可能与之相关联，从而在不知道名称的情况下移除约束，这种说法可能并不普遍正确。当逻辑上删除约束时，是否总有一种方法可以避免必须知道系统命名约束的名称？

174791 次浏览
小开
最佳答案 
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.

小开

Try:

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

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.

小开

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.

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

小开

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.

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.

小开

Something like that happened to me when I made copies of structures to temporary tables, so I removed the 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;
小开

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.

Steps involved of dropping unnamed constraint linked to column 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;


提交答案