15

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?

Positonic
  • 9,151
  • 14
  • 57
  • 84
  • 2
    If you have a locations table, would it not make sense to just have lat/long as part of this table? What benefit are you getting from splitting the table? You even make reference to using a 1-1 relationship, is there any reason to split this data? – jimplode Nov 21 '11 at 16:28
  • 1
    Can you provide the current tables' definitions? – ypercubeᵀᴹ Nov 21 '11 at 16:33
  • See here: http://stackoverflow.com/questions/1722741/defining-a-one-to-one-relationship-in-sql-server/ – ypercubeᵀᴹ Nov 21 '11 at 16:37
  • jimplode - There's no good reason. It was like that when I found it and we don't have time to re-factor right now. – Positonic Nov 21 '11 at 18:25
  • I agree with @np-hard. I think the error you are receiving is because you already have data in the locations_lonlat table that violate the FK. – Chris Dunaway Nov 21 '11 at 19:06

3 Answers3

13

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.

Mark S. Allen
  • 171
  • 1
  • 4
4

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.

Joshua
  • 40,822
  • 8
  • 72
  • 132
  • 1
    A 1-1 relationship is mandatory on both sides _by definition_. If one side was non-manditory it would be a 1-0..1 relationship ("one to zero or one"). – onedaywhen Nov 22 '11 at 10:18
  • A 1-1 relationship should be technically non-mandatory on one side or the other to preserve a bulk-load order for the database. – Joshua Nov 22 '11 at 16:26
  • 1
    Then your definition of one-to-one is a loose one; strictly speaking, it is a a 1-0..1 relationship. Also consider that your 'technical' requirement is due to a flaw in SQL (i.e. it does not support multiple assignment) and a missing feature in SQL Server (i.e. it does not support deferrable constraints). – onedaywhen Nov 22 '11 at 16:51
  • Bulk load is not fully transactional. – Joshua Nov 22 '11 at 18:38
2

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.

np-hard
  • 5,725
  • 6
  • 52
  • 76
  • 1-1, double manditory, two tables does happen. See SQL Server row limit for one reason. – Joshua Nov 22 '11 at 03:10
  • wouldn't it be in violation of third normal form ? can you cite an a link or example of why sql server would need it ? – np-hard Nov 22 '11 at 03:19
  • http://blogs.lessthandot.com/index.php/DataMgmt/DBProgramming/MSSQLServer/sql-2000-when-8000-characters-is-not-eno – Joshua Nov 22 '11 at 04:39
  • Incidentally, I hit another case that wasn't row limit where I determined in the end that normalization itself broke down and could not express the entity in third normal form. Not that you could be expected to know that. I didn't know it was possible either. – Joshua Nov 22 '11 at 04:41
  • "there is always a principal and dependent" -- not if the business rules require otherwise. Consider a legal partnership between two people where both are equal partners. I suspect "you have some normalization issues, since they belong to same table" is nonsense. Consider that such a table could not be in 6NF, being the highest normal form which is always achievable. – onedaywhen Nov 22 '11 at 10:22
  • Also a blob table and a table holding stats, timestamps etc. about each blob may be separate for performance reasons however they both exist together and cannot exist independently. – The Lonely Coder Jan 27 '15 at 14:55