0

I'am new at DB design and sorry for this noob question.I have 2 simple tables and they have 1 to 1 relation between each other. Which table's PK should be FK in other table ? How can i decide that ?

Tartar
  • 5,149
  • 16
  • 63
  • 104
  • Is the relationship a true `1 :: 1`? (i.e. every row in table A will have a related row in table B - **and vice versa**?) Or a `1::0..1` relationship? (i.e. every row in B will have a related row in A but the rows in A may not (all) have a row in B) – ypercubeᵀᴹ Dec 01 '13 at 19:40
  • @ypercube It is a true one to one relationship. – Tartar Dec 01 '13 at 19:44

2 Answers2

2

In order to decide this, the question to ask is whether the relationship is a true 1 :: 1 or not.

If the relationship is a 1 :: 0..1 (i.e. every row in B will have a related row in A but the rows in A may not (all) have a row in B), then there should be a FOREIGN KEY in table B (pk) that REFERENCES A (pk). This is the most common situation and it is easy to be implemented in almost all DBMS.

If the relationship is a true 1 :: 1 (i.e. every row in table A will have a related row in table B - and vice versa), then there should be two foreign keys, one from B towards A and another from A towards B. This is also easy to declare but not so trivial to actually make it work.

The problem arises due to the chicken-and-egg problem: Which table should I first insert a row into? If we insert into A first, the foreign key (towards B) will forbid the insert. If we insert into B first, the foreign key (towards A) will forbid the insert this time.

In order to make this work, the 2 insert statements have to be in one transaction - and the foreign keys have to have been defined as DEFERRABLE - i.e. checked at the end of the transaction. So, if you work in SQL-Server (or MySQL) you simply can't do this. If you are in Postgres or Oracle or any other DBMS that has implemented deferrable constraints, this is achievable.

ypercubeᵀᴹ
  • 113,259
  • 19
  • 174
  • 235
1

Which table's PK should be FK in other table?

Both. For a true "1 to 1" you'll need circular foreign keys (which also implies you'll need deferred foreign keys to break "chicken-and-egg" problem when inserting new data, which not all DBMSes support).

Or just merge both tables into one table (which is recommended in most cases).

However, I suspect you are not talking about true "1 to 1", but "1 to 0 or 1", in which case the "0 or 1" endpoint should contain the foreign key, for example:

CREATE TABLE ONE (
    ID INT PRIMARY KEY
);

CREATE TABLE ZERO_OR_ONE (
    ID INT PRIMARY KEY REFERENCES ONE (ID)
);
Branko Dimitrijevic
  • 50,809
  • 10
  • 93
  • 167