One to Many MySQL [duplicate]

Question

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.

Auberta

user (id, username, location); location (id, locationname, user); if user exists in location, delete prior record. – Farrel

As a matter of fact, let me ask this question so I understand it better. How does MySQL know that it is a one-many relationship vs a one-one or many-many? That might be where I'm getting confused, because I feel that I understand it (creating the relationships) just not how they work. – Auberta

@James: It's all in how the tables and foreign keys (FKs) are created. A one-to-one should be created so that the tables share the same Primary key (PK) with one table referencing the other as a FK. A one-to-many is created so that the table on the many side has a different PK and a FK column that points to the PK on the table on the one side. A many-to-many is created, as we discussed in your earlier question, by introducing a third table and essentially creating two one-to-many relationships. – Dampen

Answer 1

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.

Welldressed

Answer 2

There is an example here that is almost exactly what you need foreign keys in innodb

CREATE TABLE parent (
  id INT NOT NULL,
  PRIMARY KEY (id)
) ENGINE=INNODB;

CREATE TABLE child (
  id INT,
  parent_id INT,
  INDEX par_ind (parent_id),
  FOREIGN KEY (parent_id) REFERENCES parent(id) ON DELETE CASCADE
) ENGINE=INNODB;

In your example user is the same as parent (a user has many locations, a parent has many childs) and location is the same as child (a location has one user, a child has one parent)

Filariasis