CODEWITHSUNDEEP
plsqlnulloracle-sqldeveloperoracle12c
SandeepChander
SandeepChander

Reputation: 21

cannot drop NOT NULL constraint on a DEFAULT ON NULL column

I have a table A with schema.

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

I wanted Table_ID to be an auto-incrementing column so I created a sequence.

create sequence table_id minvalue 1 start with 1 cache 10;

and I modified the Table_ID column as

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

now I cannot drop the table or the column or the constraint.

It is giving me this error.

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

I have tried to purge the recyclebin but it is not working either.

I read other posts but none of this seems to make sense.

Please help.

Upvotes: 0

Views: 887

Answers (2)

Lucian Lazar
Lucian Lazar

Reputation: 82

If you just need to remove the constraint, just set a default value on that column. The constraint will be removed automatically:

alter table a modify table_id default 0;

Full example:

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

Upvotes: 2

Lucian Lazar
Lucian Lazar

Reputation: 82

I ran this code on Oracle Database 19c Standard Edition 2 Release 19.0.0.0.0 - Production and it worked fine:

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.

Upvotes: 0

Related Questions