Possible Duplicate:
MySQL Relationships
I am trying to create a one to many relationship in MySQL with foreign keys.
Two tables, user and location. Each user can have many locations, but each location can have only one user.
How do I configure this? I am using HeidiSQL if that helps, though I can input code as well.
MySQL does not know, nor does it need to know if a relationship is 1-1, or 1-many.
No SQL supports many-many relationships, all require a intermediate table which splits a many-many relationship into 2 separate 1-many.
The difference is in the logic that controls the relationships, which is in the code that you write.
A 1-1 relationship is maintained by having the tables share the same primary key (PK).
With the secondary table declaring that PK as a foreign key pointing to the other tables PK.
Table chinese_mother (
id integer primary key,
name....
Table chinese_child (
id integer primary key,
name ....
....,
foreign key (id) references chinese_mother.id
The direction of the relationship 1 -> many vs many <- 1 is determined by the location of the link field.
Usually every table has a unique id and the link field is called tablename_id.
The table that has the link field in it is the many side of the relationship, the other table is on the 1 side.
Each user can have many locations, but each location can have only one user.
Table user
id: primary key
name......
.....
Table location
id: primary key
user_id foreign key references (user.id)
x
y
.......
By placing the link field in the location table, you force things so that a location can only have 1 user. However a user can have many locations.