Seed foreign key constrain in knex

Question

I am trying to create the following tables with foreign keys in knex:

Comments

+----+---------+-------------------+------------+------------+------------+-----------+
| id | post_id | comment           | is_deleted | createdAt  | updatedAt  | deletedAt |
+----+---------+-------------------+------------+------------+------------+-----------+
| 1  | 2       | This is a comment | false      | 16.10.2017 | 16.10.2017 |           |
+----+---------+-------------------+------------+------------+------------+-----------+

Post

+----+-----------------+------------------+---------+------------+------------+-----------+
| id | titel           | description      | deleted | createdAt  | updatedAt  | deletedAt |
+----+-----------------+------------------+---------+------------+------------+-----------+
| 1  | This is a titel | Test Description | false   | 16.10.2017 | 16.10.2017 |           |
+----+-----------------+------------------+---------+------------+------------+-----------+
| 2  | Titel Test      | Test Description | false   | 16.10.2017 | 16.10.2017 |           |
+----+-----------------+------------------+---------+------------+------------+-----------+

I created the following migrations:

comments.js

exports.up = function (knex, Promise) {
  return knex.schema.createTable("comments", function (t) {
    t.increments("id").unsigned().primary().references('id').inTable('posts')
    t.text("comment").nullable()
    t.boolean("is_deleted").nullable()
    t.dateTime("createdAt").notNull()
    t.dateTime("updatedAt").nullable()
    t.dateTime("deletedAt").nullable()
  })
}

posts.js

exports.up = function (knex, Promise) {
    return knex.schema.createTable('posts', function (t) {
        t.increments('id').unsigned().primary();
        t.string('title').notNull();
        t.text('description').nullable();
        t.boolean('deleted').nullable();       
        t.dateTime('createdAt').notNull();
        t.dateTime('updatedAt').nullable();
        t.dateTime('deletedAt').nullable();
    });
};

Finally I am trying to seed the tables with fake data:

const faker = require("faker")
const knex = require("../db/knexfile.js")
const _ = require("lodash")
const postNumber = 50
const commentNumber = 150


function getRandomPostId() {
  const numberOfPosts = knex("posts").count("title")
  return _.random(0, numberOfPosts)
}

exports.seed = function(knex, Promise) {
  return Promise.all([
    knex("posts").del()
    .then(function() {
      const posts = []
      for (let index = 0; index < postNumber; index++) {
        posts.push({
          titel: faker.lorem.sentence(),
          description: faker.lorem.sentence(),
          createdAt: faker.date.recent(),
          updatedAt: faker.date.recent(),
          deletedAt: faker.date.recent(),
          deleted: faker.random.boolean(),
          tags: faker.random.arrayElement(["tag1", "tag2", "tag3", "tag4", ]),
        })
      }
      return knex("posts").insert(posts)
    }),
    knex("comments").del()
    .then(function() {
      const comments = []
      for (let index = 0; index < commentNumber; index++) {
        comments.push({
          comment: faker.lorem.sentence(),
          createdAt: faker.date.recent(),
          deletedAt: faker.date.recent(),
          updatedAt: faker.date.recent(),
          is_deleted: faker.date.recent(),
        })
      }
      return knex("comments").insert(comments)
    })

  ])
}

However, I get the following error:

Using environment: development
Error: ER_NO_REFERENCED_ROW_2: Cannot add or update a child row: a foreign key constraint fails (`c9`.`comments`, CONSTRAINT `comments_id_foreign` FOREIGN KEY (`id`) REFERENCES `posts` (`id`))
    at Query.Sequence._packetToError (/home/ubuntu/workspace/node_modules/mysql/lib/protocol/sequences/Sequence.js:52:14)
    at Query.ErrorPacket (/home/ubuntu/workspace/node_modules/mysql/lib/protocol/sequences/Query.js:77:18)
    at Protocol._parsePacket (/home/ubuntu/workspace/node_modules/mysql/lib/protocol/Protocol.js:279:23)
    at Parser.write (/home/ubuntu/workspace/node_modules/mysql/lib/protocol/Parser.js:76:12)
    at Protocol.write (/home/ubuntu/workspace/node_modules/mysql/lib/protocol/Protocol.js:39:16)
    at Socket.<anonymous> (/home/ubuntu/workspace/node_modules/mysql/lib/Connection.js:103:28)
    at emitOne (events.js:96:13)
    at Socket.emit (events.js:188:7)
    at readableAddChunk (_stream_readable.js:176:18)
    at Socket.Readable.push (_stream_readable.js:134:10)
    at TCP.onread (net.js:547:20)

Is my foreign key constraint wrong?
Any suggestions why I am getting this error?

I appreciate your reply!

Answer 1

Looks like you need to correct your mapping for comments table as shown below to add constraints on post_id column. Your mapping seems have created foreign key (comment's table id refers to post's id) incorrectly.

exports.up = function (knex, Promise) {
  return knex.schema.createTable("comments", function (t) {
    t.increments("id").unsigned().primary()
    t.integer("post_id").references('id').inTable('posts')
    t.text("comment").nullable()
    t.boolean("is_deleted").nullable()
    t.dateTime("createdAt").notNull()
    t.dateTime("updatedAt").nullable()
    t.dateTime("deletedAt").nullable()
  })
}

Second, you need to consider that migration/seed files are picked in natural order of their name. In your case, comments.js file is getting executed earlier than posts.js file which causing the insertion failed with obvious reason.

To solve this issue, you may rename post.js to 20170812_10_posts.js and comments.js to 20170812_20_comments.js (for e..g). The prefix <date>_<seq>_ is just merely a suggested convention. You may follow any convention while naming migration/seed files as long as their name are in intended natural order.

Related Issues: https://github.com/tgriesser/knex/issues/993

Hopes this helps.

Answer 2

In terms of migration creation, try separating the post_id foreign key generation. It looks like it's attempting to use comments.id as both the primary and foreign key, rather than a separate column named post_id that references posts.id

Migrations:

exports.up = function (knex, Promise) {
    return knex.schema.createTable('posts', function (t) {
        t.increments().unsigned().primary();
        t.string('title').notNull();
        t.text('description').nullable();
        t.boolean('deleted').nullable();       
        t.dateTime('createdAt').notNull();
        t.dateTime('updatedAt').nullable();
        t.dateTime('deletedAt').nullable();
    });
};

exports.up = function (knex, Promise) {
  return knex.schema.createTable("comments", function (t) {
    t.increments().unsigned().primary();
    t.text("comment").nullable();
    t.boolean("is_deleted").nullable();
    t.dateTime("createdAt").notNull();
    t.dateTime("updatedAt").nullable();
    t.dateTime("deletedAt").nullable();
    // column with name post_id references posts.id
    t.foreign("post_id").references('id').inTable('posts');
    // or
    // t.foreign("post_id").references('posts.id');
  })
};

Regarding the error, it looks like this is mostly a timing issue. Promise.all() does NOT run/execute/start the promise tasks in sequential order. This would mean that when a comment is created, it may not have a valid posts.id to associate to. With the update migration, you'd ideally wait until all posts have been created, grab the existing post id values, and use those values to create comments with a valid post_id constraint. The Knex method pluck() comes in handy here as you can get back an array of the posts.id values. I'd also consider maybe breaking this up into multiple seeds as I've experienced issues inserting more than ~100 into a given table as bulk. You can get around this by inserting on each loop, it will take longer but doesn't seem to experience the same limits. If every comment needs to be associated with a post, that doesn't seem to be happening with the current seed, there doesn't look to be any post_id or similar being inserted. The code below grabs a valid random posts.id on each iteration and assigns it to comments.post_id, satisfying the constraint.

The order for seeding from what I can see would be as follows:

1. Delete comments
2. Delete posts
3. Create posts
4. Create comments using valid/existing post id values

Seed:

exports.seed = function(knex, Promise) {
    return knex("comments").del()
        .then(() => {
            return knex("posts").del();
        })
        .then(() => {
            const posts = [];

            for (let index = 0; index < postNumber; index++) {
                posts.push({
                    title: faker.lorem.sentence(),
                    description: faker.lorem.sentence(),
                    createdAt: faker.date.recent(),
                    updatedAt: faker.date.recent(),
                    deletedAt: faker.date.recent(),
                    deleted: faker.random.boolean(),
                    tags: faker.random.arrayElement(["tag1", "tag2", "tag3", "tag4", ]),
                });
            }

            return knex("posts").insert(posts);
        })
        .then(() => {
            return knex('posts').pluck('id').then((postIds) => {
                const comments = [];

                    for (let index = 0; index < commentNumber; index++) {
                        comments.push({
                            comment: faker.lorem.sentence(),
                            createdAt: faker.date.recent(),
                            deletedAt: faker.date.recent(),
                            updatedAt: faker.date.recent(),
                            is_deleted: faker.date.recent(),
                            post_id: faker.random.arrayElement(postIds)
                        })
                    }

                return knex("comments").insert(comments);
            });                
        });
};

Note: within the seed, title in the post creation was misspelled as titel. Not sure if you mean to use titel over title, but it would need to be consistent.

Hopefully this helps!