Mysql auto insert record in primary table if not exist

foreign keyMySQLprimary-keytrigger

Is it possible to auto insert a record into the primary table if the record does not exist when adding a foreign key?

For example, assume these tables:
– user(id, name, age)
– topic(id, name)
– post(userId, topicId, text, createdAt, updatedAt)

Now i am pulling posts from some source and saving the records in the post table. But sometimes the data that is being returned contains a userId or a topicId that is not yet in my database. So everytime i would have to check if the user and topic records exist then save if not. Only then my post record would be valid and saved.

I want to be able to save the post even if its related user or topic does not exist, and add an empty row with the in these tables having the ids that have been stored in the post table.

Example:

Current User Table

+----+------+-----+
| id | name | age |
+----+------+-----+
| 15 | Paul | 26  |
+----+------+-----+
| 56 | John | 31  |
+----+------+-----+

current Topic Table

+----+----------+
| id | name     |
+----+----------+
| 5  | Business |
+----+----------+
| 12 | General  |
+----+----------+

current Post Table:

+--------+---------+----------------+-------------+-------------+
| userId | topicId | text           | createdAt   | updatedAt   |
+--------+---------+----------------+-------------+-------------+
| 15     | 12      | blah blah blah | *timestamp* | *timestamp* |
+--------+---------+----------------+-------------+-------------+
| 56     | 5       | lorem ipsum... | *timestamp* | *timestamp* |
+--------+---------+----------------+-------------+-------------+

So then i fetch post from some sources an get a new 1 This is a new topic posted by a user with id 72 in a topic with id 2. The source only returns the id, and to obtain the rest of the details of the user, i should make another request to their api.

Post Table after:

+--------+---------+---------------------+-------------+-------------+
| userId | topicId | text                | createdAt   | updatedAt   |
+--------+---------+---------------------+-------------+-------------+
| 15     | 12      | blah blah blah      | *timestamp* | *timestamp* |
+--------+---------+---------------------+-------------+-------------+
| 56     | 5       | lorem ipsum...      | *timestamp* | *timestamp* |
+--------+---------+---------------------+-------------+-------------+
| 72     | 2       | This is a new topic | *timestamp* | *timestamp* |
+--------+---------+---------------------+-------------+-------------+

User Table After:

+----+------+-----+
| id | name | age |
+----+------+-----+
| 15 | Paul | 26  |
+----+------+-----+
| 56 | John | 31  |
+----+------+-----+
| 72 |      |     |
+----+------+-----+

Topic Table after

+----+------------+
| id | name       |
+----+------------+
| 2  |            |
+----+------------+
| 5  | Business   |
+----+------------+
| 12 | General    |
+----+------------+

So now that i have this, i can make my request to their api and look for data for user with id 72 and data for topic with id 2.

Best Answer

You cannot get it automated via FOREIGN KEY.

Plan A: Use a TRIGGER.

Plan B: Use INSERT .. ON DUPLICATE KEY UPDATE ...