[Solved] How to use two primary keys into one foreign key? [closed]

by


It’s called Polymorphic relationship (association). And you can’t have a column (added_by in your case) that references two parent tables simultaneously. But what you can do to be able to use foreign key constraints is to have two nullable columns added_by_super_admin and added_by_admin only one of which will hold the value per record.

CREATE TABLE guests 
(
  id INT(11) NOT NULL AUTO_INCREMENT,
  first_name VARCHAR(55) DEFAULT NULL,
  last_name  VARCHAR(55) DEFAULT NULL,
  email VARCHAR(100) DEFAULT NULL,
  role_type INT(4) DEFAULT 4,
  start_date  DATE,
  expiry_date DATE,
  reason blob,
  added_by_super_admin INT(4) NULL,
  added_by_admin INT(4) NULL,
  password_digest VARCHAR(255) DEFAULT NULL,
  remember_token DATE DEFAULT NULL,
  PRIMARY KEY(id),
  CONSTRAINT fk_guests  FOREIGN KEY (added_by_super_admin) REFERENCES super_admins(id),
  CONSTRAINT fk_guests1 FOREIGN KEY (added_by_admin) REFERENCES admin(id)
);

Then in queries you can do

SELECT ...
       COALESCE(s.id, a.id) added_by_id,
       COALESCE(s.name, a.name) added_by_name,
       ...
  FROM guests g LEFT JOIN super_admins s
    ON g.added_by_super_admin = s.id LEFT JOIN admin
    ON g.added_by_admin = a.id

1

solved How to use two primary keys into one foreign key? [closed]