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

Comments

+----+---------+-------------------+------------+------------+------------+-----------+
| id | post_id | ment           | is_deleted | createdAt  | updatedAt  | deletedAt |
+----+---------+-------------------+------------+------------+------------+-----------+
| 1  | 2       | This is a ment | 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:

ments.js

exports.up = function (knex, Promise) {
  return knex.schema.createTable("ments", function (t) {
    t.increments("id").unsigned().primary().references('id').inTable('posts')
    t.text("ment").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 mentNumber = 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("ments").del()
    .then(function() {
      const ments = []
      for (let index = 0; index < mentNumber; index++) {
        ments.push({
          ment: faker.lorem.sentence(),
          createdAt: faker.date.recent(),
          deletedAt: faker.date.recent(),
          updatedAt: faker.date.recent(),
          is_deleted: faker.date.recent(),
        })
      }
      return knex("ments").insert(ments)
    })

  ])
}

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`.`ments`, CONSTRAINT `ments_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!

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

Comments

+----+---------+-------------------+------------+------------+------------+-----------+
| id | post_id | ment           | is_deleted | createdAt  | updatedAt  | deletedAt |
+----+---------+-------------------+------------+------------+------------+-----------+
| 1  | 2       | This is a ment | 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:

ments.js

exports.up = function (knex, Promise) {
  return knex.schema.createTable("ments", function (t) {
    t.increments("id").unsigned().primary().references('id').inTable('posts')
    t.text("ment").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 mentNumber = 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("ments").del()
    .then(function() {
      const ments = []
      for (let index = 0; index < mentNumber; index++) {
        ments.push({
          ment: faker.lorem.sentence(),
          createdAt: faker.date.recent(),
          deletedAt: faker.date.recent(),
          updatedAt: faker.date.recent(),
          is_deleted: faker.date.recent(),
        })
      }
      return knex("ments").insert(ments)
    })

  ])
}

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`.`ments`, CONSTRAINT `ments_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!

• javascript
• node.js
• knex.js

Share Improve this question Follow asked Oct 16, 2017 at 12:29 Carol.KarCarol.Kar 5,2353838 gold badges148148 silver badges298298 bronze badges

Add a ment  | 

2 Answers 2

Sorted by: Reset to default 4 +100

In terms of migration creation, try separating the post_id foreign key generation. It looks like it's attempting to use ments.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("ments", function (t) {
    t.increments().unsigned().primary();
    t.text("ment").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 ment 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 ments with a valid post_id constraint. The Knex method pluck() es 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 ment 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 ments.post_id, satisfying the constraint.

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

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

Seed:

exports.seed = function(knex, Promise) {
    return knex("ments").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 ments = [];

                    for (let index = 0; index < mentNumber; index++) {
                        ments.push({
                            ment: 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("ments").insert(ments);
            });                
        });
};

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!

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

exports.up = function (knex, Promise) {
  return knex.schema.createTable("ments", function (t) {
    t.increments("id").unsigned().primary()
    t.integer("post_id").references('id').inTable('posts')
    t.text("ment").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, ments.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 ments.js to 20170812_20_ments.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./tgriesser/knex/issues/993

Hopes this helps.