Check constraints is not working when inserting new record in mysql-CodePudding

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');

CodePudding user response：

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

CodePudding user response：

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))

)