How to refer 2 columns from a single column in mysql

Question

I have 3 tables they are
1. User { id, name, mobile}
2. Temp_User { id, name, mobile}
3. In_Out { id, intime, outtime}

Now I want to make id in In_Out as a foreign key and it should refer User.id as well as Temp_User.id. That is, the id value must be present either in User or in Temp_User.

Is it possible in mysql? If anyone knows, please give me the ALTER command for the In_Out table strcture. I have already created the table. Any help appreciated.

Yesterday, the last FK post I saw, was this duplicate. The day before, the previous FK post I saw, also was. Part of my comment was "The general case of "common fields used by several tables" is subtyping/inheritance/polymorphism in databases/SQL and is also asked about in questions about multiple/many/two FKs/references/relationships/links to multiple/many/two tables. (But FKs are not usually what the constraints are in that design variant, which is likely related to what you're pointing out.) Essentially, the supertype is things having those fields." — philipxy, Feb 20 '17 at 14:28
@philipxy I searched google but with inurl:stackoverflow but I found different Questions like, `refer same element from multiple tables` likewise, which is exactly opposite of my question. But this solved my problem. Thanks — Sagar V, Feb 20 '17 at 14:30

score 3 · Accepted Answer · edited Jun 14 '17 at 17:11

3

Consider adding a flag to the User table that indicates if the user is a temporary user or not:

alter table user add is_temp tinyint unsigned default 0 not null;

This will allow a simple foreign key reference from In_Out:

alter table In_Out 
    change id User_id <Match Data Type And Nullness Here>,
    add constraint User_id_fk foreign key (User_id) references User(id);

edited Jun 14 '17 at 17:11

Sagar V

12,158
7
41
68

answered Feb 20 '17 at 12:47

Russell Smith

153
1
5

How to refer 2 columns from a single column in mysql

1 Answers1