Can I create foreign key constraints from singe FK to multiple PK

Question

Currently, I have two configuration tables that stores two different kinds of configurations, however, both have an ID column. I have one table called Deactivation. Any configuration I want to deactivate I can add ID of that configuration table into this table.

Structure of the database is as follow.

My question is Can I create two FK constraints that refer,

- ID (FK) to T1_ID (PK)
- ID (FK) to T2_ID (PK)

(single child to multiple parents)

So, deactivation table should not be allowed insertion of any id which is not in either T1 or T2. But If ID found in any of that table insertion should be done.

Thanks

You should create T1_ID and t2_ID field in Deactivations and put foreign key for each other. If you want to use same ID: first it can create conflict if both table has same ID sequences. Second you can't make foreign key. — Zeki Gumus, Dec 10 '18 at 10:08
@ZekiGumus thanks for this suggestion. However, I am unable to change the structure of the tables. My concern is to restrict unnecessary IDs insertion into Deactivation tables. Is there any workaround with this structure? — kandarp, Dec 10 '18 at 10:44
A FK is when values must appear in a certain other spot. That's not what you want. So don't use "FK" for that. Using two FKs from two fields is a faq--google re multiple/many/two FKs to multiple/many/two tables. But it's usually an anti-pattern for subtyping/inheritance. That's a faq too. Google re sql/database subtyping/inheritance/polymorphism. Please always google many clear, concise & specific versions/phrasings of your question/problem/goal with & without your particular strings/names & read many answers. PS If you can't change the tables add a table of all ids or use triggers. — philipxy, Dec 10 '18 at 10:48
Possible duplicate of [How can you represent inheritance in a database?](https://stackoverflow.com/questions/3579079/how-can-you-represent-inheritance-in-a-database) — philipxy, Dec 10 '18 at 10:50
Please [use text, not images/links, for text (including code, tables & ERDs)](https://meta.stackoverflow.com/q/285551/3404097). Use a link/image only for convenience to supplement text and/or for what cannot be given in text. And never give a diagram without a legend/key. — philipxy, Dec 10 '18 at 10:54
@philipxy I will keep that in mind regarding 'not use images' — kandarp, Dec 10 '18 at 10:58
@philipxy your suggested thread shows inheritance (single parent to multiple children) however, in my case its multiple parents to a single child. — kandarp, Dec 10 '18 at 11:00
It would actually help more if, rather than using T1 and T2, you described more clearly the types of real world objects/data being modelled here — Caius Jard, Dec 10 '18 at 11:09
The entity type of each of the two tables is a subtype of the entity type of the one table. Each of the 2 tables holds values of one *type* of value that goes in the one table. Your "child of" means "is a subtype of". Try some of the designs. — philipxy, Dec 10 '18 at 11:16

score 0 · Answer 1 · answered Dec 10 '18 at 11:06

I think you've got your answer already, from the comments, but here as a direct statement:

Relational databases Foreign/Primary key mechanism is not intended to maintain and enforce this kind of relationship between tables. You'll have to do something custom with a trigger on Deactivations, that checks the value being inserted into Deactivations is present in T1 or T2 and permits the insert if it is/rejects it if not.

You'll also have to consider coding up something in T1 and T2 to deal with updates/deleted (cascade them?) and collisions (what if T1 id = 1 and T2 id = 2, but then you change T2 ID to also be 1? What if an ID present in T1, representing one thing, is inserted into T2 representing a different thing? Does the new thing in T2 automatically become deactivated? Does my phone stop working because someone stopped paying their contract on their tablet?)

Gordon Linoff · Answer 2 · 2018-12-10T11:32:10.687

I think your relationship is backwards. Basically, you want deactivations (or some other table) to have the primary key. Then, this can be shared among the other tables:

create table deactivations (
     deactivationId int auto_increment primary key,
     . . .
);

create table config1 (
     config1Id int primary key,
     . . . 
     constraint fk_config1_config1Id foreign key (config1Id) references deactivations(deactivationId)
);

create table config2 (
     config2Id int primary key,
     . . . 
     constraint fk_config2_config2Id foreign key (config2Id) references deactivations(deactivationId)
);

This is not 100% what you need, because a deactivationId could be shared between the two tables. There are ways around this, but alas in MySQL, such a check requires triggers.

If you still want to go with your solution, you can use a generated column:

create table deactivations (
    config1Id int,
    config2Id int,
    id int generated always as (coalesce(config1Id, config2Id)),
    constraint fk_deactivations_config1Id as foreign key (config1Id) references config1(config1Id),
    constraint fk_deactivations_config2Id as foreign key (config2Id) references config2(config2Id)
);

Unfortunately, MySQL does not support check constraints, so you require a trigger to guarantee that exactly one but not both of the ids are not null.

Can I create foreign key constraints from singe FK to multiple PK

2 Answers2