Composite Foreign Key Question

Question

I am trying to figure out if it is possible to create a foreign key in this way.

First there are two parent tables in this scenario.

Table 1 has a PK int and another int column. When combined the two are unique.

Table 2 has a PK int and another int column. When combined the two are unique.

The paired value between the two ints is how we currently select the child records for either table.

Table 3 has a value for the PK and other int column from one of the first two tables. It is reliably selectable because the two fields when both used in a search are unique to one of the above tables.

So I am looking to create a FK or possibly two FK for this scenario. I would like to be able to do cascading deletes from either of the first tables into the third table, and would also like to use this for the entity relationships in Entity Framework.

Please let me know if this is not making sense. I have read it several times, and it is about as clear as I can get it.

Thanks

Answer 1

If I understand you correctly, you have 3 tables structured as follows:

TABLE_A:
    PK_FIELD int NOT NULL
    OTHER_FIELD int NOT NULL, NOT IN TABLE_B.OTHER_FIELD

TABLE_B:
    PK_FIELD int NOT NULL
    OTHER_FIELD int NOT NULL, NOT IN TABLE_A.OTHER_FIELD

TABLE_C:
    PK_FIELD int NOT NULL
    OTHER_FIELD int NOT NULL IN (TABLE_A.OTHER_FIELD OR TABLE_B.OTHER_FIELD

What you are trying to do is define TABLE_C such that you could cascade updates and deletes between the tables. Because of how TABLE_A.OTHER_FIELD and TABLE_B.OTHER_FIELD are defined, you will guarantee that there is no overlap between them. The problem is figuring out which table has the reference to TABLE_C.

What you can do with this definition is cascade from TABLE_A or TABLE_B to TABLE_C since the relationship is quite clear in that direction. However, to cascade from TABLE_C up is more complicated, since you don't know which table to cascade to. Either define two fields in TABLE_C one to reference each of the other tables, and then you can define the relationship between all three tables, knowing that for any given record there will only be one or two tables involved, never all three. Alternatively, you can use code to figure out which table to relate this particular row to and cascade accordingly.

Answer 2

I don't know which dbms you are using but I don't know any that accept multi-parents. What you can do is to define an index on table3 (just to be faster) and implement the cascading delete in your application.

Answer 3

If Table3 contains all values in Table1 UNION Table2, then you have an inheritance scheme:

Table3 { (int id, int type) PK }
Table1 { (int id, int type [CHECK type = 1]) FK Table3 }
Table2 { (int id, int type [CHECK type = 2]) FK Table3 }

With that, deleting from Table3 will cascade to Table1 or Table2 appropriately.

Otherwise, if Table3 is just a subset of Table1 UNION Table2 - then introducing Table4 as the full set would work.

Table4 { (int id, int type [CHECK type IN (1,2)]) PK }
Table1 { (int id, int type [CHECK type = 1]) FK Table4 }
Table2 { (int id, int type [CHECK type = 2]) FK Table4 }
Table3 { (int id, int type) FK Table4 }

To handle cascaded deletes, though, you'd need to delete from Table4 (though you could do a trigger on Table3 to handle that for you).

Edit (because I think it's an important point that should be seen, seperate from the comments):

Yeah, the problem with this is that one and two are the parents. They both have related records in 3, which would be a FK, not a PK for that table. It seems to be pretty much impossible using a FK constraint. Thanks for the response! – Chrisb (an hour ago)

IMO - your data model is broken then. Table1 and Table2 are unrelated, but you're trying to stuff them into the same column in Table3. If Table1 and Table2 are related somehow, then you need to model that.

Either introduce a parent table (Table4) if they're related, or either a 2nd column in Table3 (Table1 FK, Table2 FK) or 2 join tables if they're not. Don't try to put a round peg into a square hole - and don't try to make up for it with a trigger. ;)