mysql中foreign keys_MySQL与FOREIGN KEYS的多对多关系

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?

解决方案

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