ForeignKey to a table based on a condition.

Using Django
1

Assume I have 2 tables Table A (assume it contains a single column with values A1, A2, A3 etc) & Table B (assume it contains a single column with values B1, B2, B3 etc). In Django, these will be CharField with the values being select choices

I want to create a 3rd Table Table C with the following conditions.

  1. Have a CharField with 2 select choices A & B.
  2. If it’s matches A, have a ForeignKey to Table A and if it matches B, have a ForeignKey to Table B.

I want to know how do I achieve such a thing.

(I hope my problem statement is clear. It’s a generalized statement of what I am trying to achieve. I can’t reveal what the actual problem is because reasons)

PS: I forgot to tell that I am hoping there is a way to achieve it using django models.

Thank you :slightly_smiling_face:

2

So depending upon the real situation, there are at least two possible solutions.

  • The “database purist” in me would say you should have two separate columns, one each Foreign Key to Table A and Table B, with the appropriate logic to ensure that the one not being used is null.

  • You can also use the Generic Relations facility to have a “single” field that can relate to one of multiple tables.
    (Note: I have used - actually still use - generic foreign keys, and have learned that they’re not nearly as useful or helpful as one might think. I now rely upon the multiple column solution unless the GFK has a significantly obvious benefit.)

3

The 2 column solution makes sense to me. (I will also have a look at this Generic Relations).

Thank you !

4

Is there a very very good reason why you must have two tables A and B formed the way you suggest?

Otherwise, normalise your first paragraph: You have two types of response (A and B) and for each type of response there are numerous responses (A1,A2, B1,B2 etc). Thus there are two tables, lets say TableType and TableTypeResponse. TableTypeResponse has a FK to TableType.

Table C, then has two columns, Type and TypeResponse with FK’s to the two respective tables. When you enter data into the Type column it is used to filter the TypeResponse column.

If this latter scenario looks like what you’re trying to achieve, do it. Conditional FKs is a very very bad idea.