CODEWITHSUNDEEP
c#sqlsql-server
Positonic
Positonic

Reputation: 9411

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

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?

Upvotes: 23

Views: 29692

Answers (4)

ADM-IT
ADM-IT

Reputation: 4180

The best option is to have a foreign key in conjunction with unique record, that way you guarantee that there is no more references but only one.

See example (ONE student can hold only ONE book):

Student
  - Id
  - FullName

Book
  - Id
  - Title
  - HeldByStudentId (Unique) (FK points to Student.Id)

Another approach is to keep the ONE-to-ONE relationship information in separate table:

Student
  - Id
  - FullName

Book
  - Id
  - Title

RentedBook
  - StudentId (Unique) (FK points to Student.Id)
  - BookId (Unique) (FK points to Book.Id)

Both approaches are fine, it depends whether you want to keep the relationship information aside or not.

I hope it helped.

Upvotes: 0

Mark S. Allen
Mark S. Allen

Reputation: 181

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 (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 an RI 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 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.

Upvotes: 14

np-hard
np-hard

Reputation: 5815

Even in a 1:1 relationship, there is always a principal and dependent, principal entity being the one that exist independently, if both cannot exist independently then you have some normalization issues, since they belong to same table.

a true one to one is where tables share primary keys, where principal's primary key is the auto increment one, and dependent's key has a foreign key reference to principals

i think what you have is a 1:M relationship, where location having many longlat (as per the database), but you could make the locations_lonlat.LID unique.

reason you are getting error i think is that there is existing data where locations_lonlat.LID has some values that dont correspond to location.id table.

Upvotes: 4

Joshua
Joshua

Reputation: 43259

A 1-1 relationship looks like

CREATE TABLE tbl1 ( id ... PRIMARY KEY (id))
CREATE TABLE tbl2 ( id ... PRIMARY KEY (id),
    FOREIGN KEY fk_tbl2_tbl1 REFERENCES tbl1(id))

Where the foreign key is declared on the non-manditory side (if there is one). 1-1 double non-manditory requires that foreign key be separate column than primary key.

Upvotes: 4

Related Questions