Oracle Check constraint violated

Viewed 26

i created a table named employee with check constraints and when i'm trying to execute insert statement, i'm getting an error

SQL>

create table employee(
    empno char(10) primary key,
    empname varchar2(30),
    deptno char(5), 
    foreign key (deptno) references department,
    mobile char(12), 
    hire_date date,
    job varchar2(15),
    edlevel Smallint,
    gender char(1),
    dob date,
    salary number(10,2),
    bonus number(10,2),
    comm number(10,2),
    constraint eno check(empno in 'E%'),
    constraint mof check(gender in ('m','f')),
    constraint salcheck check(salary>0)
);

Table created.

SQL> desc employee;

 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 EMPNO                                     NOT NULL CHAR(10)
 EMPNAME                                            VARCHAR2(30)
 DEPTNO                                             CHAR(5)
 MOBILE                                             CHAR(12)
 HIRE_DATE                                          DATE
 JOB                                                VARCHAR2(15)
 EDLEVEL                                            NUMBER(38)
 GENDER                                             CHAR(1)
 DOB                                                DATE
 SALARY                                             NUMBER(10,2)
 BONUS                                              NUMBER(10,2)
 COMM                                               NUMBER(10,2)

the value that i have given for employee starts with E which satisfies the constraint, yet i am facing this error.

SQL>

insert into employee values('E0010','ARNOLD LEONARD AMON','A00',9445876534,
                            '1-Dec-2000','PRESIDENT',18,'m','19-Jun-1967',
                            152750,10000,14220);
insert into employee values('E0010','ARNOLD LEONARD AMON','A00',9445876534,
                            '1-Dec-2000','PRESIDENT',18,'m','19-Jun-1967',
                            152750,10000,14220)

ERROR at line 1:

ORA-02290: check constraint (SYSTEM.ENO) violated

1 Answers

Your check says: empno in 'E%' this is equivalent to empno = 'E%' which is not true for your insert.

You seem to be looking for empno like 'E%'

Related