3

I have:

Table Review with column item_id (not pk)

Table Product with column id (pk)

Table Business with column id (pk)

Relationships:

Review.item_id references Product.id

Review.item_id references Business.id

These relationships are 1:M enter image description here

What is the syntax in MySQL for one column field to reference IDs from two different tables?

My research lead me to believe you cannot have one foreign key reference two different tables. My conclusions so far (based on links provided below):

  1. Use a foreign key without a foreign key constraint
  2. Use field to reference other records, but do not create a foreign key constraint
  3. Polymorphic Associations. The foreign key may reference rows in any of several related tables.
  4. One foreign key can reference only one table

These conclusions seem logical to me, but I can't figure out the syntax.

links:

MYSQL - One Column Referenced to Multiple Table

Is it possible to reference one column as multiple foreign keys?

MySQL - Conditional Foreign Key Constraints

Damir Sudarevic
  • 21,891
  • 3
  • 47
  • 71
fadingbeat
  • 355
  • 3
  • 16
  • 1
    If you don't care about referential integrity there's no need for FKs. But without knowing what you are trying to achieve its' not possible to advise. It may be that the FKs should be on pro and bus rather than rev. (great descriptive table name btw) – P.Salmon Apr 27 '20 at 11:21
  • Thank you both. I am reading the first link, and I have adjusted the table names and added a simple db schema that describes my situation. – fadingbeat Apr 27 '20 at 11:49
  • 1
    (1/2) The way *you worded the problem*, these are simply two foreign keys. However logically it does not sound good because you claim that `product = business` – Damir Sudarevic Apr 27 '20 at 13:25
  • 1
    (2/2) Simply reword your "business problem" in plain language. – Damir Sudarevic Apr 27 '20 at 13:26
  • 1
    (3/(2+1)) It is very likely that you are trying to *enforce a logical error* in your model, so it is a good thing that you are having trouble with it. Unless, *business* and *product* are synonyms in the model. – Damir Sudarevic Apr 27 '20 at 15:31
  • 1
    You can't get FKs to do what you want. Clearly say what you want & why--don't misuse "FK" to try to say it. You *can* have 2 FKs to 2 different places--but a FK says, values in the FK table must appear in the referenced table, not what you want. PS Why are those links there? What do they have to do with your question? (Rhetorical.) – philipxy Apr 27 '20 at 16:13
  • 1
    This is a faq. Before considering posting please read the manual & google any error message or many clear, concise & precise phrasings of your question/problem/goal, with & without your particular strings/names & site:stackoverflow.com & tags; read many answers. If you post a question, use one phrasing as title. Reflect your research. See [ask] & the voting arrow mouseover texts. PS Clearly say what you want without misusing terrms. Still, there are tons of badly phrased versions of this like 'many FKs/references to many tables" & it's a common anti-pattern for DB/SQL subtyping/inheritance. – philipxy Apr 27 '20 at 16:19
  • Run the link the other way. – Rick James May 07 '20 at 03:46
  • Thank you everyone for comments, all are useful. It turned out that the schema has changed, so this solution I was searching for is not necessary anymore. However, I got some good points to consider for future reference. – fadingbeat May 07 '20 at 14:53

0 Answers0