Can we refer to two different tables for one foreign key?

Question

I have these tables:

table1
-----------------------
tb1_id
name
other stuff

table2
-------------------------------
tb2_Id
other stuff

table 3
--------------------------------
id
ref Id    ->either tb2_id or tb1_id

Can this be achieved from the below code ?

CREATE TABLE `eloan`.`table3` (
  `id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  `refId` VARCHAR(45) NOT NULL DEFAULT '',
  PRIMARY KEY(`id`),
  CONSTRAINT `refId` FOREIGN KEY `refId` (`refId`, `refId`)
    REFERENCES `table2` (`tb2_id`, `tb1_id`)
    ON DELETE RESTRICT
    ON UPDATE RESTRICT
)
ENGINE = InnoDB;

This code returned a "duplicate redid" error.

Answer 1

No. That's not possible.

If you want to use just a single refId column, as you show, you will not be able to declare/define foreign key constraint(s) to reference more than one table.

You may be able to define BEFORE INSERT, BEFORE UPDATE and BEFORE DELETE triggers on the three tables, to perform some checks of integrity, and have the trigger throw an exception/error to prevent some changes.

---

Obviously, you could define two separate columns in table3, one can be a foreign key reference to table1 the other can reference table2. You can define foreign key constraints.

You can allow both of the columns to be NULL.

If you want to enforce only one or the other column to be populated (at least one of the columns has to be NULL and the other column has to be NOT NULL), you can enforce that in BEFORE INSERT and BEFORE UPDATE triggers.