Search code examples
sqloracle11gconstraintsstring-length

Check length constraint is not working in Oracle11g sqlplus

I've been trying to put length constraint , so that it would not take string whose length is more or less than 5

Create Table Statement:
create table exp(id char(10),name varchar(50));

Add Constraint Statement:
alter table exp add constraint exp1 check(length(id)=5);

Insert Statement:
insert into exp(id,name) values('10001','Abhi');

But whenever i try to insert data like the above written it shows

insert into exp(id,name) values('10001','Abhi')
*
ERROR at line 1:
ORA-02290: check constraint (VIT.EXP1) violated
Solution

  • Change char(10) to varchar2(10):

    create table exp(id varchar2(10),name varchar(50));

    A char(10) column has always a length of 10. Regardsless your insert statement. That's why you get the error.