如果不存在，主表中的Mysql自动插入记录

Question

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.

Answer 1

People can have strong opinions on this and we can respectfully disagree.

In reference to a comment saying people do this (knowingly loading blank and null junk in tables) all the time as seen in the post Here.

I said:

That reference is a consortium of people out of their minds. Writing a post saying what people want to hear, putting a lollipop in their mouths, does not make for a decent answer. In fact, it can be pretty irresponsible. This OP is doing things in the wrong order. Put stuff in some staging tables, call the other APIs, get stuff in clean, that makes me sleep well at night. Referential Integrity has a meaning. We don't twist it and confuzzle everyone just to please them.

Part of our responsiblity is doing the right thing, in the right order, to keep our data clean and supporting Referential Integrity. And to steer our peers toward the same versus anything contrary. Sort of the Prime Directive.