不能删除DEFAULT on NULL列上的NOT NULL约束

Question

我有一个表A与schema。

Name     Null?    Type         
-------- -------- ------------ 
NAME              VARCHAR2(10) 
TABLE_ID NOT NULL NUMBER 

我想让Table_ID是auto-incrementing列，所以我创建了一个序列。

create sequence table_id minvalue 1 start with 1 cache 10;

，我将Table_ID列修改为

alter table A
modify table_id default on null table_id.nextval;

现在我不能删除表或列或约束。

它给了我这个错误.

An error was encountered performing the requested operation:
ORA-30667: cannot drop NOT NULL constraint on a DEFAULT ON NULL column
30667.0000 -  "cannot drop NOT NULL constraint on a DEFAULT ON NULL column"
*Cause:    The NOT NULL constraint on a DEFAULT ON NULL column could not be
dropped.
*Action:   Do not drop the NOT NULL constraint on a DEFAULT ON NULL column.
The only way to drop the constraint is to remove the ON NULL
property of the column default.
Vendor code 30667

我已经尝试清除回收站，但它也不工作.

我读了其他的帖子，但这些似乎都没有意义。

请帮助。

Answer 1

如果您只需要删除约束，只需在该列上设置默认值。约束将被自动删除:

alter table a modify table_id default 0;

完整的示例:

create table a(name varchar2(10), table_id number not null);
create sequence table_id minvalue 1 start with 1 cache 10;
alter table a modify table_id default on null table_id.nextval;
select * from user_constraints where table_name = 'A'; -- one constraint "TABLE_ID" IS NOT NULL
alter table a drop constraint SYS_C0026367; -- ORA-30667: cannot drop NOT NULL constraint on a DEFAULT ON NULL column
alter table a modify table_id default 0;
alter table a drop constraint SYS_C0026367; -- ORA-02443: Cannot drop constraint  - nonexistent constraint
select * from user_constraints where table_name = 'A'; -- the constraint was dropped automatically

Answer 2

我在Oracle Database 19c Standard Edition 2 Release 19.0.0.0.0 - Production上运行了这段代码，它工作得很好:

create table a(name varchar2(10), table_id number not null);
create sequence table_id minvalue 1 start with 1 cache 10;
alter table a modify table_id default on null table_id.nextval;
drop table a;
Table A created.
Sequence TABLE_ID created.
Table A altered.
Table A dropped.