mysql insert from two tables into one using inner join and max()

Question

I checked the suggested posts but couldn't find an answer. I am making a CRUD application with a many to many relationship and three MySQL tables. One table is called "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 |
+-------+---------+------------------------+---------------+

Another is called "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     |
+---------------+------------+

The third is 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          |
+---------------+-------+---------------+

Below is the SQL I have now. With for instance "oranges" at the end I want to get - into one new row of "join_table" - the max pm_id from "pm" into the pm_id column and the searchword_id that corresponds to oranges from "searchwords" into the searchword_id column.

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

This SQL has magically worked sometimes but usually not (how is that kind of inconsistency even possible?). The pm_id value always gets in there so I tend to get a row with the correct value as pm_id and searchword as NULL as seen above. Help would be much appreciated!!

Answer 1

Actually I just solved it, using:

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

Inner joins are so 2017 anyway, right?

But now I'm having problems when trying to delete a post and got an error:

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

Sigh