I don't know what is causing this problem. Can anybody help me find it out? Also Oracle live doesn't specify which line is causing the problem.

create table EMP (
    EMPNO number(4),
    ENAME varchar2(10),
    EJOB varchar2(9) constraint def_job default 'CLRK',
    MGR_ID number(4) constraint supervisor references EMPNO,
    BIRTH_DATE date,
    SAL number(7,2) constraint salCheck check(SAL > 20000) constraint defSal default 20001,
    COMM number(7,2) constraint defComm default 1000,
    DEPTNO varchar2(3) constraint deptFk references DPT(DNO),
    PRJ_ID varchar2(9) constraint defa_prjID default 'P1', 
    DATE_OF_JOIN date,
    
    constraint pri_ky primary key(EMPNO)
);

Error: ORA-02253: constraint specification not allowed here

CodePudding user response：

A default value is not a constraint, so for example

constraint def_job default 'CLRK'

should just be

default 'CLRK'

Also, constraints need to reference a table and optionally a unique column, so

constraint supervisor references EMPNO

should be

constraint supervisor references emp(empno)

It's good practice, or at least a good idea, not to specify a datatype for foreign keys, allowing them to inherit from the parent column.

This works (after creating a dept table):

create table dpt (dno integer primary key);

create table emp (
    empno           number(4) constraint pri_ky primary key,
    ename           varchar2(10),
    ejob            varchar2(9) default 'CLRK',
    mgr_id          constraint supervisor references emp(empno),
    birth_date      date,
    sal             number(7,2) default 20001 constraint salcheck check(sal > 20000),
    comm            number(7,2) default 1000,
    deptno          constraint deptfk references dpt(dno),
    prj_id          varchar2(9) default 'P1', 
    date_of_join    date
);