3

I want to have base parent table with few disjoint subtypes. To illustrate what I mean:

CREATE TABLE issues (
  issue_id SERIAL PRIMARY_KEY,
  issue_type CHAR(1) CHECK (issue_type IN ('B', 'F')),
  UNIQUE KEY (issue_id, issue_type)
);

CREATE TABLE bugs (
  issue_id INT PRIMARY_KEY,
  issue_type CHAR(1) CHECK (issue_type = 'B'),
  ...,
  FOREIGN KEY (issue_id, issue_type) REFERENCES issues(issue_id, issue_type)
);

CREATE TABLE features (
  issue_id INT PRIMARY_KEY,
  issue_type CHAR(1) CHECK (issue_type = 'F'),
  ...,
  FOREIGN KEY (issue_id, issue_type) REFERENCES issues(issue_id, issue_type)
);

To achieve this in MySQL, I tried to use ENUMs instead of unsupported CHECKs:

CREATE TABLE issues (
  issue_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  issue_type ENUM('B', 'F') NOT NULL,
  UNIQUE (issue_id, issue_type)
);

CREATE TABLE bugs (
  issue_id INT NOT NULL PRIMARY KEY,
  issue_type ENUM('B') NOT NULL DEFAULT 'B',
  text TEXT,
  FOREIGN KEY (issue_id, issue_type) REFERENCES issues(issue_id, issue_type)
);

CREATE TABLE features (
  issue_id INT NOT NULL PRIMARY KEY,
  issue_type ENUM('F') NOT NULL DEFAULT 'F',
  text TEXT,
  FOREIGN KEY (issue_id, issue_type) REFERENCES issues(issue_id, issue_type)
);

The issue is with the features table where I'm not able to insert any row, because of ERROR (1452): Cannot add or update a child row: a foreign key constraint fails ...

-- OK
INSERT INTO issues (issue_id, issue_type) VALUES (1, 'B');
INSERT INTO issues (issue_id, issue_type) VALUES (2, 'F');
INSERT INTO bugs(issue_id, issue_type, text) VALUES (1, 'B', 'bar');
-- ERROR (1452): Cannot add or update a child row: a foreign key constraint fails (`...`.`features`, CONSTRAINT `features_ibfk_1` FOREIGN KEY (`issue_id`, `issue_type`) REFERENCES `issues` (`issue_id`, `issue_type`) ON DELETE CASCADE ON UPDATE CASCADE)
INSERT INTO features(issue_id, issue_type, text) VALUES (2, 'F', 'baz');

I guess this is caused by the incompatible ENUM definitions in issues and features table.

My question is what do you see as best alternative to do this?

I can think of these options:

  1. create issue_types table and reference it from all tables; in bugs and features check allowed values in trigger
  2. instead of ENUMs use BIT(1) and check allowed values in trigger (without the list in separate table)

Your Answer

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

Browse other questions tagged or ask your own question.