I have this innoDB table with two columns in Mysql:
CREATE TABLE mytable (
`id_1` mediumint(8) unsigned NOT NULL,
`id_2` mediumint(8) unsigned NOT NULL
) ENGINE=InnoDB;
And I want to force a check on INSERT operations: id_1 must be less than id_2 So I tried with this but it didn't work out:
ALTER TABLE mytable ADD CONSTRAINT mycheck CHECK (id_1 < id_2)
Is this even possible? What I did wrong? Thanks!
Unfortunately MySQL don't support CHECK CONSTRAINT and thus your ALTER statement won't be of any use. Per MySQL documentation it parses but ignores the CHECK clause. Though you can get the similar effect using a BEFORE INSERT TRIGGER. Something like below
delimiter //
CREATE TRIGGER trig_check BEFORE INSERT ON mytable
FOR EACH ROW
BEGIN
IF NEW.id_1 > NEW.id_2 THEN
//Throw Error Probably
END IF;
END
//
delimiter ;