Why I can't name the default constraint here? I need to drop it afterwards

July 18, 2022

I have a table Article with some constraints

And i’m trying to create it like,

    CREATE TABLE Article(
    ArCode char(5) CONSTRAINT arcode_chk check(ArCode like 'A%') CONSTRAINT ar_code_pk primary key, 
    ArName varchar2(20) CONSTRAINT ar_name_nn not null, 
    Rate number(8,2),
    Quantity number(4) CONSTRAINT qty_df default 0 CONSTRAINT qty_chk check(Quantity>=0),
    Classs char(1) CONSTRAINT cls_chk check(Classs in('A','B','C'))
    );

When i add a constraint name for the default constraint like i above mentioned it gives me an error

Error at line 5/24: ORA-02253: constraint specification not allowed here
ORA-06512: at "SYS.WWV_DBMS_SQL_APEX_220100", line 847
ORA-06512: at "SYS.DBMS_SYS_SQL", line 1658
ORA-06512: at "SYS.WWV_DBMS_SQL_APEX_220100", line 833
ORA-06512: at "APEX_220100.WWV_FLOW_DYNAMIC_EXEC", line 1903


3.     ArName varchar2(20) CONSTRAINT ar_name_nn not null, 
4.     Rate number(8,2),
5.     Quantity number(4) CONSTRAINT qty_df default 0 CONSTRAINT qty_chk check(Quantity>=0),
6.     Classs char(1) CONSTRAINT cls_chk check(Classs in('A','B','C'))
7. );

But if I remove the constraint name for default, it executes successfully. But I have to drop the default constraint at some point. Any alternate ideas? or Am I just doing it wrong?

>Solution :

Oracle doesn’t have that option, i.e. you can NOT name the default constraint.

If you want to modify it later (either to set a different default value, or remove it entirely), use ALTER TABLE.

Here’s an example.

Setting the constraint:

SQL> create table test
  2    (id    number,
  3     name  varchar2(20) default 'LF');

Table created.

SQL> insert into test (id) values (1);

1 row created.

As expected, name got the default value:

SQL> select * from test;

        ID NAME
---------- --------------------
         1 LF

To remove it, set default null:

SQL> alter table test modify name default null;

Table altered.

What is column’s value in this case?

SQL> insert into test (id) values (2);

1 row created.

SQL> select * from test;

        ID NAME
---------- --------------------
         1 LF
         2                         --> no default value any more

SQL>