2

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.

user2707590
  • 1,066
  • 1
  • 14
  • 28
  • you are trying to insert a row in the primary table with a foreign key which does not yet exist? – Zeromus Jul 20 '16 at 07:39
  • @Zeromus, yes i am. is it possible to do it via triggers or anything else? – user2707590 Jul 20 '16 at 07:46
  • So remove the foreign key constraint. It's silly to have FK constraint only to have it modified by a trigger in order to let your insert pass. Get rid of it, then you don't need the trigger. And then I'd rethink about the whole logic of your system. – Mjh Jul 20 '16 at 07:50
  • @Mjh - i'm pulling data from apis, so it's obvious that soem records won't exist on my system. I am not modifyng the FK constraint, i just need to add a blank record in the primary table for that FK record. – user2707590 Jul 20 '16 at 07:54
  • ah well if you can insert a blank record just make the foreign key column nullable and you are set – Zeromus Jul 20 '16 at 07:56
  • Yeah, make it nullable if referential integrity was meant to be a joke anyway – Drew Jul 20 '16 at 08:05
  • But you don't need referential integrity. The whole point of referential integrity is that you prevent adding records that shouldn't be there. Since you **want** records to be inserted regardless, you don't **need** referential integrity. You can set your fk to be nullable, like the guys mentioned, but if you really think about it - it's useless in your use scenario. – Mjh Jul 20 '16 at 08:07
  • I was joking, or pointing out how senseless it is to think the data is clean. Garbage in, garbage out. – Drew Jul 20 '16 at 08:09
  • beg to differ, here an answer that explains when you would need a nullable fk http://stackoverflow.com/questions/7573590/can-a-foreign-key-be-null-and-or-duplicate – Zeromus Jul 20 '16 at 08:11
  • 1
    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. – Drew Jul 20 '16 at 08:19
  • @Drew you should make an answer out of your comment, I agree completely with you. OP is doing things in the wrong order and answer referenced by Zeromus is ridiculous, it just shows that there can be many smart people gathered at one place and be completely wrong. – Mjh Jul 20 '16 at 08:28

1 Answers1

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.

Community
  • 1
  • 1
Drew
  • 24,851
  • 10
  • 43
  • 78