Mysql – Two primary keys to one foreign key

foreign keyMySQLprimary-key

racing_couple
ID (PRIMARY)

breeding_couple
ID (PRIMARY)

egg
IDpar(FOREIGN KEY)

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?

ID(racing_couple)
1
2

ID(breeding_couple)
1
2

IDpar(egg)
1 (ID racing_couple)
1 (ID breeding_couple)
2 (ID racing_couple)
2 (ID breeding_couple)

Best Answer

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.

Related Solutions

Mysql – Foreign Key Constraint fails

You need to have already declared the table that the foreign key references, before you can define a foreign key that references it.

Once you declare the second table, you can then declare the first table. Tested here on MySQL 5.5.27.

If you need to bypass the validation, you can do this:

SET FOREIGN_KEY_CHECKS = 0;
-- declare tables
SET FOREIGN_KEY_CHECKS = 1;

http://dev.mysql.com/doc/refman/5.5/en/server-system-variables.html#sysvar_foreign_key_checks

Postgresql – Need for indexes on foreign keys

Your design is good. If you are having a performance problem (which you can't know at design time), you should create an index on column table1.ref_field, in the same order (ASC) as the table2.id column. This will improve performance on joins between those to tables/columns. There is overhead to maintaining any index, so you want to weigh that cost against the benefit of improved performance.

PostgreSQL doesn't automatically create such indexes on foreign key columns that reference other columns, unfortunately, so you have to do it yourself.

Here is a StackOverflow question on the same topic:

Postgres and Indexes on Foreign Keys and Primary Keys

Here is a query to help determine where you might benefit from adding such an index:

Postgresql: Indexes on Foreign Keys

Best Answer

Related Solutions

Mysql – Foreign Key Constraint fails

Postgresql – Need for indexes on foreign keys

Related Question