FK constraint fails while inserting into child on Disjoint tables

Question

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.

Answer 1

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

Answer 2

This is just a complimentary remark to @McNets answer, it's not a critique of his answer, just a note on why ENUM is a bad choice of data type. Let's add the label table to the equation:

CREATE TABLE label (
    item_id INT NOT NULL,
    item_type ENUM('label', 'checkbox') 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 ;

This works in the sense that we can now add an item of type label, and a corresponding label. However, nothing prevents us from adding a row like:

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

So, the ENUM type is of no use when it comes to type control.