0

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.

NicolasCanac
  • 57
  • 1
  • 5
  • Why do you need table C if `id1 == id2` all the time? – Barmar Aug 02 '14 at 05:18
  • 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? – Gerd Wagner Aug 18 '14 at 10:41

1 Answers1

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.

prashant thakre
  • 5,061
  • 3
  • 26
  • 39
  • 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? – Gerd Wagner Aug 18 '14 at 10:42
  • @NicolasCanac this article may shed some light on the reasons behind prashant's answer → http://stackoverflow.com/questions/337503/whats-the-best-practice-for-primary-keys-in-tables – xmojmr Aug 21 '14 at 15:44