0

Constraints check(age >= 18)is not working, New record should not be insert with age value 10. New record should only insert when age value is greater than and equal to 18.

I have created a table with SQL command given below.

create table studentInfo_tbl(
id int not null auto_increment unique,
name varchar(50) not null,
age int(3) not null check(age >= 18),
gender varchar(1) not null default 'm',
address varchar(100),
primary key(id)
);

Insert record command is working and inserting age value 10.

insert into studentinfo_tbl(name, age, gender, address) 
values('Ali', 10, 'm', 'New York');
Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
Billu
  • 2,733
  • 26
  • 47
  • 3
    Are you using MySQL version 8.0.16 or later? Earlier versions of MySQL didn't support check constraints. This question may be a duplicate of https://stackoverflow.com/questions/2115497/check-constraint-in-mysql-is-not-working – Bill Karwin Aug 11 '22 at 18:55
  • 1
    As stated before, older versions of MySQL accepted the syntax for `CHECK` but didn't honor it. – The Impaler Aug 11 '22 at 19:11

2 Answers2

0

For us using version 5.7 or other ones below 8.0.16, we still have triggers .

delimiter //
create trigger check_age before insert on studentInfo_tbl for each row
begin
if new.age <18 then
signal  sqlstate value '99999' set message_text = 'Must be over 18 years old to qualify.';
end if;
end//
delimiter ;

insert into studentInfo_tbl(name, age, gender, address) 
values('Ali', 10, 'm', 'New York');
-- result: Error Code: 1644. Must be over 18 years old to register.

insert into studentInfo_tbl(name, age, gender, address) 
values('Ali', 18, 'm', 'New York');
-- result: 1, Ali, 18, m, New York
blabla_bingo
  • 1,825
  • 1
  • 2
  • 5
0

If you are on mysql 8+ see the syntax below

CREATE TABLE xxx (
  .
  .
  age int NOT NULL
  CONSTRAINT studentInfo_tbl_chk_1 CHECK ((age >= 18))

)
jmvcollaborator
  • 2,141
  • 1
  • 6
  • 17