Mysql – FK constraint fails while inserting into child on Disjoint tables

constraintforeign keyMySQL

I'm trying to test the concept of disjoint tables in MySQL, so I created some tables:

CREATE TABLE item (
  id int NOT NULL AUTO_INCREMENT,
  type ENUM('label', 'checkbox') NOT NULL DEFAULT 'label',
  PRIMARY KEY (id, type)
  ) ENGINE = InnoDB ;

CREATE TABLE label (
  item_id INT NOT NULL,
  item_type ENUM('label') NOT NULL DEFAULT 'label',
  reviewer INT NOT NULL,
  PRIMARY KEY(item_id, item_type, reviewer),
  CONSTRAINT `fk_1` FOREIGN KEY (item_id, item_type) 
                    REFERENCES `item` (id, type) ON DELETE NO ACTION ON UPDATE CASCADE
  ) ENGINE = InnoDB ;

CREATE TABLE checkbox (
  item_id INT NOT NULL,
  item_type ENUM('checkbox') NOT NULL DEFAULT 'checkbox',
  reviewer INT NOT NULL,
  PRIMARY KEY(item_id, item_type, reviewer),
  CONSTRAINT `fk_2` FOREIGN KEY (item_id, item_type) 
                    REFERENCES `item` (id, type) ON DELETE NO ACTION ON UPDATE CASCADE
  ) ENGINE = InnoDB ;

I had no problems inserting into the parent table:

INSERT INTO item VALUES(1,'checkbox');

But if I then try to insert into the child table:

INSERT INTO checkbox VALUES(1,'checkbox',1);

I get a "constraint failed" error:

#1452 – Cannot add or update a child row: a foreign key constraint fails (checkbox, CONSTRAINT fk_2 FOREIGN KEY (item_id, item_type) REFERENCES item (id, type) ON DELETE NO ACTION ON UPDATE NO ACTION)

I've re-read my DDL several times and I can't find what is it that I'm doing wrong.
What am I missing?

I tried this in sqlfiddle.com, which means MySQL 5.6.

Also, since I have a box with 5.0.45 and phpMyAdmin at hand, I tried inserting with the GUI (in case I was messing up something else) but got the same results.

Best Answer

Equal both type ENUM values.

CREATE TABLE `item` (
  id int NOT NULL AUTO_INCREMENT,
  type ENUM('label', 'checkbox') NOT NULL DEFAULT 'label',
  PRIMARY KEY (id, type)
  );
CREATE TABLE checkbox (
  item_id INT NOT NULL,
  item_type ENUM('label', 'checkbox') NOT NULL DEFAULT 'checkbox',
  reviewer INT NOT NULL,
  PRIMARY KEY(item_id, item_type, reviewer),
  CONSTRAINT `fk_2` FOREIGN KEY (item_id, item_type) 
                    REFERENCES `item` (id, type) ON DELETE NO ACTION ON UPDATE CASCADE
  );
INSERT INTO item VALUES(1,'checkbox');
INSERT INTO checkbox VALUES(1,'checkbox', 1);

dbfiddle here