how to refer to two different table with one attribute in mysql?

Question

Here is the query I have attempted:

create table login_user
(
    user_id int(3) AUTO_INCREMENT,
    password varchar(10),
    role varchar(10),
    no_ic varchar(12),
    primary key(user_id),
    CONSTRAINT fk_product
    FOREIGN KEY (no_ic) REFERENCES supplier(no_ic),
    CONSTRAINT fk_customer
    FOREIGN KEY (no_ic) REFERENCES customer(no_ic)
);

I have tried this query but it fails.

https://stackoverflow.com/questions/15935402/sql-server-foreign-key-to-multiple-tables — Ehsan Waris, Nov 01 '17 at 10:35
Possible duplicate of [SQL Server foreign key to multiple tables](https://stackoverflow.com/questions/15935402/sql-server-foreign-key-to-multiple-tables) — flu, Nov 01 '17 at 10:47

score 0 · Answer 1 · answered Nov 01 '17 at 10:56

AS the guys pointed up, this is a duplicated question BUT anyway : "A foreign key can only reference one table"

If you want to work around your stuation, you can create one table that have suppliers and customers and add one column if '1' supplier if '0' customer

Then use FOREIGN KEY (no_ic) REFERENCES newTableName(no_ic)

how to refer to two different table with one attribute in mysql?

1 Answers1