set maximum value to a column

I hav开发者_JAVA百科e a table with a column (int type) called age. This column should hold maximun value 50. If it exceeds then it shouldn't update that row.

Means this column shold take values from 0 to 50.

If I try to update that to 51 then that shouldn't allow.

Could any one help....!

Try this:

CREATE TRIGGER check_trigger
  BEFORE INSERT
  ON table
  FOR EACH ROW
BEGIN
  IF NEW.age<0 OR NEW.age>50 THEN
    CALL `Error: Wrong values for age`; -- this trick will throw an error
  END IF;
END

create table test (
age tinyint not null ) engine = myisam;

delimiter //
drop trigger if exists max_num//
create trigger max_num before insert on test
for each row 
begin
if new.age < 0 or new.age > 50 then
set new.age = null;
end if;
end//
delimiter ;

insert into test (age) values (100);

Make the same thing for update.

You could use CHECK constraint:

CREATE TABLE person (
Name VARCHAR(80), 
Age  INT CHECK (Age BETWEEN 5 and 50));

MySQL introduced check constraints in MySQL 8.0.16 and now the check constraints are enforced as you would reasonably expect. See MySQL 8.0.16 introduced check constraints. So, you can achieve your goal by altering your age column using the statement below.

ALTER TABLE `YOUR_TABLE_NAME` MODIFY `age` TINYINT(2) UNSIGNED NOT NULL DEFAULT 0 CHECK(`age` <= 50);