How to prevent that two references share the same primary key

Question

I have one common table that is referenced by two other tables in a database. Primary key of that two other tables is also a foreign key that points to a value of a primary key of common table.

I was wondering is there a way, MySQL could prevent that both tables have a same value as a reference? My idea is something like this:

logins instance should be only in one table (students or professors)

Login row should be referenced in one of two tables (students or professors), but not in both.

I'm having a hard time understanding your purpose. Can you show an example of what you want to accomplish? — Filipe Silva, Aug 08 '13 at 22:34
I think this may be a dupe of http://stackoverflow.com/q/4969133/743382 (and if so, the answer there looks good to me), but I too am not sure if I am reading your question correctly. — , Aug 08 '13 at 22:35

Bill Karwin · Answer 1 · 2013-08-09T16:07:36.770

CREATE TABLE common (
  id INT PRIMARY KEY,
  type CHAR(1) NOT NULL,
  UNIQUE KEY (id, type)
);

INSERT INTO common (id, type) VALUES (1, 'A'), (2, 'B');

CREATE TABLE typeA (
  id INT PRIMARY KEY,
  type CHAR(1) NOT NULL DEFAULT 'A',
  FOREIGN KEY (id, type) REFERENCES common(id, type)
);

INSERT INTO typeB (id, type) VALUES (1, 'A'); -- OK
INSERT INTO typeB (id, type) VALUES (2, 'A'); -- FAIL

CREATE TABLE typeB (
  id INT PRIMARY KEY,
  type CHAR(1) NOT NULL DEFAULT 'B',
  FOREIGN KEY (id, type) REFERENCES common(id, type)
);

INSERT INTO typeB (id, type) VALUES (1, 'B'); -- FAIL
INSERT INTO typeB (id, type) VALUES (2, 'B'); -- OK

You need some way to enforce type to the right value in each child table. For example a trigger, or another foreign key to a lookup table. MySQL does not support CHECK constraints in any storage engine.

How to prevent that two references share the same primary key

1 Answers1