Search code examples
sqloracle-databaseplsqlconstraintsdefault

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


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>