1

I want the insertion statement to fail if the the column year is not 2014 or 2015

I need to do this from the database side as a temporary mesearure until i can get access to the application code

CREATE TABLE `movies` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(345) DEFAULT NULL,
  `year` int(11) DEFAULT NULL,
  `imdbid` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4545 DEFAULT CHARSET=utf8;
124697
  • 22,097
  • 68
  • 188
  • 315

1 Answers1

3

Add a check constraint 

CREATE TABLE `movies` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(345) DEFAULT NULL,
  `year` int(11) DEFAULT NULL,
  `imdbid` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`id`),
CHECK `year` in (2014,2015)
) ENGINE=InnoDB AUTO_INCREMENT=4545 DEFAULT CHARSET=utf8;

or

ALTER TABLE `movies`
ADD constraint checkyear check (`year`in (2014,2015))

EDIT

Based on updates from jarlh, CHECK constraint does not play well with mysql. as per MySQL Reference Manual

The CHECK clause is parsed but ignored by all storage engines

You can create a Trigger to simulate a CHECK constraint. Something like this.

CREATE TRIGGER trg BEFORE INSERT ON movies
FOR EACH ROW BEGIN
IF (New.year NOT IN(2014,2015))
THEN
    signal sqlstate '45000'
    set message_text = 'check failed';
END IF;
END;

You can refer to the following threads for more information

CHECK constraint in MySQL is not working

Can a MySQL trigger simulate a CHECK constraint?

Community
  • 1
  • 1
ughai
  • 9,830
  • 3
  • 29
  • 47
  • 1
    Someone said that MySQL doesn't handle check constraints very well. Do you know what was meant? – jarlh Apr 08 '15 at 12:53
  • @jarlh - yep, The [CHECK](http://dev.mysql.com/doc/refman/5.7/en/create-table.html) clause is parsed but ignored by all storage engines. Trigger would be a better solution – ughai Apr 08 '15 at 13:07