Foreign key can references multiple tables?

Question

I have this three tables:

CREATE TABLE userTypeA
(
  id VARCHAR (256) PRIMARY KEY,
  nickname VARCHAR (256) NOT NULL,
  password VARCHAR(70) NOT NULL DEFAULT '123456'
  --AND MORE OTHER ATTRIBUTTES DIFFERENT FROM OTHER USERTYPES
);

CREATE TABLE userTypeB
(
  id VARCHAR (256) PRIMARY KEY ,
  nickname VARCHAR (256) NOT NULL,
  password VARCHAR(70) NOT NULL DEFAULT '123456'
  --AND MORE OTHER ATTRIBUTTES DIFFERENT FROM OTHER USERTYPES
);

CREATE TABLE userTypeC
(
  id VARCHAR (256) PRIMARY KEY ,
  nickname VARCHAR (256) NOT NULL,
  password VARCHAR(70) NOT NULL DEFAULT '123456'
  --AND MORE OTHER ATTRIBUTTES DIFFERENT FROM OTHER USERTYPES
);

Now, my idea was creating a table which has a foreign key that can reference the id of one of the three tables (userTypeA, userTypeB, userTypeC). In occasions it will reference table userTypeA, on other occasions it will reference table userTypeB and in others userTypeC. I tried this, but it seems to fail:

CREATE TABLE account
(
    account_id VARCHAR (256),
    user_id VARCHAR (256),
    PRIMARY KEY (account_id),
    CONSTRAINT accounts_users_fk1 FOREIGN KEY (user_id) REFERENCES userTypeA (id),
    CONSTRAINT accounts_users_fk2 FOREIGN KEY (user_id) REFERENCES userTypeB (id),
    CONSTRAINT accounts_users_fk3 FOREIGN KEY (user_id) REFERENCES userTypeC (id),
  );

I've been looking for some examples but none looked like mine. Do you know any way to be able to reference one of the three tables with a single foreign key, or should I create different tables and structure them differently?

No, this is not possible. You shouldn't use 3 separate tables for this. You should have one main `user` table, and then subsidiary tables for the different types. — Barmar, Nov 03 '20 at 00:11
Why do you need 3 tables that are identical? Why not just a `usertype` column in the `user` table? — Barmar, Nov 03 '20 at 00:12
What you ask -- that I call "partial foreign keys" -- is one of the three improvements I would suggest to the SQL Standard. And it could be easily implemented by just adding a `WHERE` clause at the end. — The Impaler, Nov 03 '20 at 00:23
@TheImpaler, How would you think it should work if these partial foreign keys in the same column should have UNIQUE constraints on them? My point is, each column should have one type of information. This is a basic requirement of relational models. You should check out [SQL and Relational Theory: How to Write Accurate SQL Code](https://www.amazon.com/SQL-Relational-Theory-Write-Accurate-dp-1491941170/dp/1491941170/). — Bill Karwin, Nov 03 '20 at 16:02
@BillKarwin Yes, absolutely. However, as Albert Einstein once said: "In theory, theory and practice are the same. In practice, they are not". The thing is *partial foreign keys* would be really useful to enforce *concrete inheritance* in a relational database; without them it's really verbose and confusing. I would welcome a better solution, though. (by concrete inheritance I mean the parent table cannot be abstract, i.e. without children) — The Impaler, Nov 03 '20 at 16:30

The Impaler · Answer 1 · 2020-11-03T02:07:16.763

3

You can create three separate columns, each with an accompanying foreign key, as in:

CREATE TABLE account (
  account_id VARCHAR (256),
  user_id_a VARCHAR (256),
  user_id_b VARCHAR (256),
  user_id_c VARCHAR (256),
  PRIMARY KEY (account_id),
  CONSTRAINT accounts_users_fk1 FOREIGN KEY (user_id_a) REFERENCES userTypeA (id),
  CONSTRAINT accounts_users_fk2 FOREIGN KEY (user_id_b) REFERENCES userTypeB (id),
  CONSTRAINT accounts_users_fk3 FOREIGN KEY (user_id_c) REFERENCES userTypeC (id),
  CONSTRAINT chk1 CHECK (
    user_id_a IS NOT NULL AND user_id_b IS NULL AND user_id_c IS NULL OR
    user_id_a IS NULL AND user_id_b IS NOT NULL AND user_id_c IS NULL OR
    user_id_a IS NULL AND user_id_b IS NULL AND user_id_c IS NOT NULL)
);

The CHECK constraint makes sure one and only one of the columns user_id_a, user_id_b, and user_id_c is not null at any given time.

Also, please note that CHECK constraints are validated in MySQL starting in version 8.0.16. Prior to that version CHECK constraints are parsed but not stored.

edited Nov 03 '20 at 02:07

answered Nov 03 '20 at 00:20

The Impaler

45,731
9
39
76

Slight correction: prior to MySQL 8.0.16, `CHECK` constraints are parsed, but not stored. – Bill Karwin Nov 03 '20 at 01:16
@BillKarwin Fixed. Thanks, I just tried in MySQL 8.0.3 and the `CHECK` constraint is nowhere to be found. – The Impaler Nov 03 '20 at 02:07
It's important to note that `CHECK` constraints can't be used in combination with `ON DELETE` and `ON UPDATE` according to [the MySQL reference manual](https://dev.mysql.com/doc/refman/8.0/en/create-table-check-constraints.html). – Magnus Lind Oxlund Feb 27 '23 at 21:10

Foreign key can references multiple tables?

1 Answers1