mysql使用inner join和max()从两个表插入一个表

Question

我检查了建议的帖子，但找不到答案。我正在制作一个具有多对多关系和三个 MySQL 表的 CRUD 应用程序。一张表称为“pm”:

CREATE TABLE `pm` (
`pm_id` int(11) NOT NULL AUTO_INCREMENT,
`pm_name` varchar(45) NOT NULL,
`pm_address` varchar(45) DEFAULT NULL,
`valid_through` int(11) NOT NULL,
 PRIMARY KEY (`pm_id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8

+-------+---------+------------------------+---------------+
| pm_id | pm_name |       pm_address       | valid_through |
+-------+---------+------------------------+---------------+
|     1 | Alpha   | http://www.alpha.com   |        190303 |
|     2 | Bravo   | http://www.bravo.com   |        200506 |
|     3 | Charlie | http://www.charlie.com |        190708 |
|     4 | Delta   | http://www.delta.com   |        210509 |
|     5 | Echo    | http://www.echo.com    |        230416 |
|     6 | Foxtrot | http://www.foxtrot.com |        181011 |
+-------+---------+------------------------+---------------+

另一个称为“searchwords”:

CREATE TABLE `searchwords` (
`searchword_id` int(11) NOT NULL AUTO_INCREMENT,
`searchword` varchar(45) NOT NULL,
PRIMARY KEY (`searchword_id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8

+---------------+------------+
| searchword_id | searchword |
+---------------+------------+
|             1 | apples     |
|             2 | oranges    |
|             3 | pears      |
|             4 | bananas    |
|             5 | grapes     |
+---------------+------------+

第三个是join_table:

CREATE TABLE `join_table` (
`id_join_table` int(11) NOT NULL AUTO_INCREMENT,
`pm_id` int(11) DEFAULT NULL,
`searchword_id` int(11) DEFAULT NULL,
PRIMARY KEY (`id_join_table`),
KEY `FK1_idx` (`pm_id`),
KEY `FK2_idx` (`searchword_id`),
CONSTRAINT `FK1` FOREIGN KEY (`pm_id`) REFERENCES `pm` (`pm_id`) ON DELETE 
NO ACTION ON UPDATE NO ACTION,
CONSTRAINT `FK2` FOREIGN KEY (`searchword_id`) REFERENCES `searchwords` 
(`searchword_id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8

+---------------+-------+---------------+
| id_join_table | pm_id | searchword_id |
+---------------+-------+---------------+
|             8 |     6 | NULL          |
|             9 |     6 | NULL          |
|            10 |     6 | NULL          |
+---------------+-------+---------------+

下面是我现在的SQL。例如，最后的“oranges”我想进入“join_table”的新行中，将“pm”中的最大pm_id放入pm_id列中，并将与“searchwords”中的橙子相对应的searchword_id放入searchword_id列中。

INSERT INTO join_table (pm_id, searchword_id)
SELECT (SELECT MAX(pm_id) FROM pm), (SELECT DISTINCT
searchwords.searchword_id FROM searchwords
INNER JOIN join_table ON 
searchwords.searchword_id = join_table.searchword_id
WHERE searchword = "oranges");

这个 SQL 有时神奇地起作用，但通常不起作用(这种不一致怎么可能发生？)。 pm_id 值总是在那里，所以我倾向于得到一行，其中正确的值为 pm_id 且 searchword 为 NULL，如上所示。非常感谢您的帮助!!

Answer 1

实际上我刚刚解决了这个问题，使用:

INSERT INTO join_table (pm_id, searchword_id)
SELECT MAX(pm_id), searchword_id
FROM pm, searchwords
WHERE searchword="bananas"

无论如何，内部联接都是 2017 年的风格，对吧？

但现在我在尝试删除帖子时遇到问题并收到错误:

Fatal error: Uncaught PDOException: SQLSTATE[23000]: Integrity constraint 
violation: 1451 Cannot delete or update a parent row: a foreign key 
constraint fails (`database`.`join_table`, CONSTRAINT `FK1` 
FOREIGN KEY (`pm_id`) REFERENCES `pm` (`pm_id`)

叹息