Which way should a Foreign Key go in a one to one relationship

Question

I have a locations table, has a primary key of ID.

I also have locations_lonlat table, which contains a column called LID (for location id).

This is how these 2 tables are currently associated.

I want to create a foreign key on one of the tables, if I am to stick with how it currently works (easiest approach) then I should create a foreign key from locations_lonlat.LID pointing to locations.id.

When I tried to do this, I get an error

"The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "

Which I presume is because location.id is a primary key, which I can probably get around, but it seems to be that this whole approach kinda smells.

Would it not be better to have a locationLonLatId on the location table.

They have a 1-1 relationship anyway, so perhaps it doesn't matter?

What approach would be best?

Answer 1

In short, it is always better to identify the parent table (the one that records are added into first) and make the relationship from the parent to the child, even in the case of 1-to-1 relationships.

If you care about why, read further...

Situation 1

If the 1-to-1 relationship between the two tables is identifying (meaning the foreign key becomes the child's primary key), an identical data structure results in both tables, with identical primary keys. The only difference is that, if a referential integrity constraint is declared to ensure the keys are kept in synch, a record will have to be added to the one table (the "from" table) before the other.

Situation 2

If the child table has a different primary key, i.e. the relationship is non-identifying, a relationship would have to be added from parent to child in order to create the necessary foreign key attribute in the child to hold the join ID.

Situation 3

In the same situation as Situation 1 (1-to-1, with an identifying relationship between the tables) but with cardinality showing optional on the child side of the relationship, it would make sense to have the relationship go from the parent towards the child.

If the relationship is from the child to the parent, the foreign key attribute that would result in the parent table would have to be made nullable (to allow a parent record without a child record), so parent records would sometimes have a NULL in this field if there was no child record.

If the relationship is from parent to child, the foreign key is in the child table and no foreign key exists in the parent. In this case, there is either a child record (1 to 1) or there isn't (1 to 0). This results in a more efficient structure as far as storage is concerned.