Conditional foreign key in mysql

Question

I have a table named batch with entityId and entityType. EntityId can refer to multiple tables based on CHAR enum mentioned in entityType.

Is there a way to achieve linkage in MySQL with parent tables with this kind of association.

Show us your actual `CREATE TABLE` statements, not a description, as well as sample data. — Dai, Apr 21 '23 at 12:36
Are you using `CONSTRAINT ... FOREIGN KEY` correctly already elsewhere? And what version of MySQL are you using? — Dai, Apr 21 '23 at 12:36
A fk can reference only 1 table. consider a trigger (or a redesign) — P.Salmon, Apr 21 '23 at 12:40

score 1 · Answer 1 · answered Apr 21 '23 at 13:34

No, you cannot have conditional foreign key constraints. However, you can have three separate ones that can work in an exclusive way. For example:

create table s (id int primary key not null);
create table m (id int primary key not null);
create table l (id int primary key not null);

create table batch (
  size enum('S', 'M', 'L'),
  s_id int references s (id),
  m_id int references m (id),
  l_id int references l (id),
  check (size = 'S' and s_id is not null and m_id is null and l_id is null
      or size = 'M' and s_id is null and m_id is not null and l_id is null
      or size = 'L' and s_id is null and m_id is null and l_id is not null)
);

The check constraint ensures one and only one of them is not null at any given time, and that it must correspond to the value in the size column.

Note: CHECK constraints are only honored starting in MySQL 8.0.17 (or similar version). Before that they were read and stored but silently ignored.

Conditional foreign key in mysql

1 Answers1