Связывание MySQL со многими для многих с FOREIGN KEYS

Я пытаюсь создать отношения "многие ко многим" в моей базе данных MySQL. У меня три таблицы: Films, Genres и Films_Genres. Для их настройки я использую следующий код:

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
)

Однако, когда я пытаюсь вставить некоторые значения в таблицы с помощью:

INSERT INTO Films (Title) VALUES ('$title')
INSERT INTO Genres (Name) VALUES ('$genre')

Я вижу новый фильм в таблице Films и новый жанр в таблице Genres, но таблица Films_Genres не обновляется - новых строк нет (я проверяю phpMyAdmin).

Что я делаю неправильно?

Ответ 1

Вы не увидите ничего в таблице Films_Genres, пока вы явно не введете что-нибудь в него. Ссылочная целостность через PK и FK не предназначена для заполнения ваших таблиц.

Ваш код MySql для вставки новой записи в Films_Genres, если это новый фильм, соответствующий новому жанру, может выглядеть как

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);

На стороне php, чтобы получить новый присвоенный идентификатор для поля с автоинкрементами, используйте $mysqli->insert_id.

Теперь, если вы хотите создать новый фильм и назначить его сразу нескольким жанрам, вы можете сделать

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');

Вот SQLFiddle демо

Ответ 2

В принципе, вы должны вставить строку в таблицу ссылок, чтобы "связать" фильм и жанр, который вы только что вставили. Механизм Mysql не делает этого для вас автоматически (он просто не знает, что эти 2 записи должны быть как-то связаны) - это должно выполняться на стороне приложения или вручную

INSERT INTO Films_Genres (film_id,genre_id) VALUES (1,1)

Ответ 3

Вы должны заполнить таблицу "многие-ко-многим" самостоятельно, так как работает mysql, к сожалению, здесь не выполняется автоматическая догадка.