SQL - Multiple inheritance

Question

I have one "sub-table" (C) and two "super-table" (A, B):

• A (id PRIMARY KEY)
• B (id PRIMARY KEY)
• C (id1 , id2, c_col)

To make multiple inheritance, I can easily state that C.id1 refers to A.id and C.id2 to B.id by creating FOREIGN KEYs. However, owing to my model (actually because A and B are also inheritance of an other super-table), id1 and id2 will always have the same value for one occurence. My question is, do you think I need to create an additionnal "id" column in the C table as PRIMARY KEY in addition to "id1" and "id2" forein key ? More easily, can I just arbitrary pick id1 or id2 column and define it as PRIMARY KEY ?

I don't know if I'm very clear. If not, please tell me how you usually modelize multiple inheritance in SQL.

• Barmar: Why do you need table C if id1 == id2 all the time?
• Gerd Wagner: Your question is unclear. What do you mean with "A and B are also inheritance of an other super-table"? Do they represent overlapping subclasses of another class? Why didn't you reply to the comment question of @Barmar?

Answer 1

You should not arbitrary pick id1 or id2 column and define it as PRIMARY KEY. Its best to have a different id as primary key in C table. Hope its clear to you if not please update.

• Gerd Wagner: What do you mean with "a different id as primary key in C"? How do you enforce that the rows of C require corresponding rows both in A and B?
• xmojmr: @NicolasCanac this article may shed some light on the reasons behind prashant's answer → stackoverflow.com/questions/337503/…