I have 2 IDs from 2 different tables (racing_couple and breeding_couple) and they are primary keys. In 3. table (egg) I have IDpar which is foreign key and references ID in racing_couple and breeding_couple. Tables represents racing and breeding couple of pigeons, table "egg" represents egg of racing or breeding couple. And I'm using IDpar to identify which egg belongs to which couple.

Note There are other fields in both database but they are mostly varchar and not so relevant to this problem.

If I have something like this in both databases. How to know which IDpar has value from racing_couple and which IDpar has value from breeding_couple. I think I made mistake by making my database like this, but is there any way to make that work?



1 (ID racing_couple)
1 (ID breeding_couple)
2 (ID racing_couple)
2 (ID breeding_couple)
  • You say "other fields in both database" / "something like this in both databases" - which tables are where? Jul 6, 2013 at 19:01
  • See the answer by @gbn here: NULLs in a composite primary key - SQL Server Replace Cat and Dog with racing_couple and breeding_couple and for Pet you'll have to add a new couple table. Then the foreign key from egg would be to that couple table. Jul 6, 2013 at 22:35

2 Answers 2


I would suggest that you either want a single "couple" table instead of two, if the properties of each coupling are sufficiently similar, or a couple table that the two existing types of couple "inherit" from, like so:

RC                  Couple                                Egg
----------          ---------------------------           ----------------
C ID (FK)   ----->  C ID (PK)                    <-----   C ID (FK)
(rc props)    |     (generic couple properties)           (egg properties)
BC            |
----------    |
C ID (FK)   --'
(BC props)

You could still have breeding and racing details for any could in this arrangement (which might actually be fine, of course) but each egg can only be associated with one couple. You won't have problems with overlapping ID ranges as there is only one range of IDs for couples when modeled this way, and all other tables with data relating to couples only have the one FK linking to the could record.


Yes. Mistake.

One way of making this design work is to have the ID ranges not overlap - eg, have the Racing couples IDs start at 10000 (assuming you'll never get anywhere near that with the Breeding Couple IDs, of course)

Alternatively, can you add a flag to the egg table to indicate which couple table to use?

  • How will you define the foreign keys? Jul 6, 2013 at 21:25
  • Good question, and one that I see the OP did ask and I didn't answer. TBH: I wouldn't be attempting this anyway. Not like this. I would be in SQL Server, and (if I had to do it with this schema) I would probably end up enforcing integrity using triggers. Or check constraints. Jul 6, 2013 at 21:32
  • 2
    Could you share it with us?
    – hmayag
    Jul 6, 2013 at 22:41
  • Sure. The best way to correct that is to make 2 new columns in "egg" table. first column for ID from "racing_couple" and other column for "breeding_couple" so it can't mix
    – FosAvance
    Jul 7, 2013 at 12:18
  • 1
    I would not suggest the multiple parent IDs method as without extra CHECK constraints you could run into situations where bugs in your code allow both columns to have values and that makes no sense (and may not be easy to fix with confidence) Aug 5, 2013 at 23:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.