4

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.

2

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

  • Thanks. I can't believe I overlooked the possibility that the enums were simply different types. – Roflo Mar 12 '18 at 19:28
1

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.

  • 1
    To make matters worse, in MySQL:" The CHECK clause is parsed but ignored by all storage engines." – Roflo Mar 13 '18 at 15:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.