MySQL many-to-many relationship with FOREIGN KEYS

foreign-keys, many-to-many, mysql, php, sql

Solution

You won't see anything in `Films_Genres` table until you explicitly insert something in it. Referential integrity through PK and FK is not for populating your tables for you.

Your MySql code for inserting a new record in `Films_Genres`, if it's a new film which correspond to a new genre, might look like

INSERT INTO Films (Title) VALUES ('Title1');
SET @film_id = LAST_INSERT_ID();

INSERT INTO Genres (Name) VALUES ('Genre1');
SET @genre_id = LAST_INSERT_ID();

INSERT INTO Films_Genres (film_id, genre_id) VALUES(@film_id, @genre_id);

On php side to get a newly assigned id for an autoincremented field use `$mysqli->insert_id`.

Now if you want to create a new film and assign it to multiple genres at once you can do

INSERT INTO Films (Title) VALUES ('Title2');
SET @film_id = LAST_INSERT_ID();
-- if you get ids of genre from your UI just use them
INSERT INTO Films_Genres (film_id, genre_id) 
SELECT @film_id, id
  FROM Genres
 WHERE id IN (2, 3, 4);

INSERT INTO Films (Title) VALUES ('Title3');
SET @film_id = LAST_INSERT_ID();
-- if you names of genres you can use them too
INSERT INTO Films_Genres (film_id, genre_id) 
SELECT @film_id, id
  FROM Genres
 WHERE Name IN ('Genre2', 'Genre4');

Here is SQLFiddle demo

Problem

I am trying to create a many-to-many relationship in my MySQL database. I have three tables: `Films`, `Genres` and `Films_Genres`. I am using the following code to set them up: ``` CREATE TABLE Films ( id INT NOT NULL AUTO_INCREMENT, PRIMARY KEY(id), Title VARCHAR(255) ), CREATE TABLE Genres ( id INT NOT NULL AUTO_INCREMENT, PRIMARY KEY(id), Name VARCHAR(255) ), CREATE TABLE Films_Genres ( film_id INT NOT NULL, genre_id INT NOT NULL, PRIMARY KEY (film_id, genre_id), FOREIGN KEY (film_id) REFERENCES Films(id) ON UPDATE CASCADE, FOREIGN KEY (genre_id) REFERENCES Genres(id) ON UPDATE CASCADE ) ``` However, when I try to insert some values into the tables with: ``` INSERT INTO Films (Title) VALUES ('$title') INSERT INTO Genres (Name) VALUES ('$genre') ``` I can see the new Film in `Films` table and the new Genre in `Genres` table but the `Films_Genres` table doesn't update - there are no new rows (I'm checking through phpMyAdmin). What am I doing wrong?

Original source