Constraint violation error

Question

I have created this table as part of my assignment,

create table course (
    cnum char(4) primary key,
    title varchar(20),
    credits number(1)
);

The table has this check constraint cnum_ck

cnum like '[a-z][0-9][0-9][0-9]'

When I tried to insert the following row

insert into course values('m130', 'xyz', 3);

It threw check constraint cnum_ck violation error. I'm not sure where I went wrong. Please help

Answer 1

You are using SQL Server patterns for the LIKE, and these non-standard wildcards are not supported by LIKE in Oracle (nor any other database apart from Sybase). Instead, use regular expressions:

create table course (
    cnum char(4) primary key,
    title varchar(20),
    credits number(1),
    constraint chk_cnum check (regexp_like(cnum,  '^[a-z][0-9][0-9][0-9]$'))
);

insert into course values('m130', 'xyz', 3);

Here is a SQL fiddle.