Tuesday 26 February 2019

Seeding table in KnexJS foreign key constraint

I am building a NodeJS API backend and using Knex to handle DB migrations et al.

I have 3 tables: stalls, markets and bookings

In my bookings table I want to reference the id column from both the stalls and markets tables.

I have setup my bookings migration as follows:-

    exports.up = function(knex, Promise) {
  return knex.schema.createTable("bookings", table => {
    table.increments("id");
    table.string("firstName");
    table.string("lastName");
    table.string("address");
    table.string("phoneNumber");
    table.string("emailAddress");
    table
      .integer("marketDateId")
      .references("id")
      .inTable("markets");
    table
      .integer("stallTypeId")
      .references("id")
      .inTable("stalls");
    table.boolean("clothesRail");
    table.string("businessName");
    table.boolean("businessInsurance");
    table.string("items");
  });
};

exports.down = function(knex, Promise) {
  return knex.schema.dropTable("bookings");
};

Stalls:

    exports.up = (knex, Promise) => {
  return knex.schema.createTable("stalls", table => {
    table.increments("id");
    table.string("stallType");
  });
};

exports.down = (knex, Promise) => {
  return knex.schema.dropTable("stalls");
};

Markets:

exports.up = (knex, Promise) => {
  return knex.schema.createTable("markets", table => {
    table.increments("id");
    table.string("marketDate");
  });
};

exports.down = (knex, Promise) => {
  return knex.schema.dropTable("markets");
};

I have seeded the stalls and markets tables with some dummy data, but when I try to seed the bookings table using the below seed file:

bookings.js

exports.seed = (knex, Promise) =>
  knex("bookings")
    .del()
    .then(() =>
      knex("bookings").insert([
        {
          firstName: "Test",
          lastName: "Tester",
          phoneNumber: "07123 123456",
          emailAddress: "test@lb-dev.io",
          marketDateId: "7",
          stallTypeId: "7",
          clothesRail: false,
          items: "Testing Stuff"
        },
        {
          firstName: "Test",
          lastName: "Testington",
          phoneNumber: "07123 123456",
          emailAddress: "test@lb-dev.io",
          marketDateId: "8",
          stallTypeId: "8",
          clothesRail: false,
          businessName: "TestCrafts",
          items: "Testing Stuff"
        },
        {
          firstName: "Test",
          lastName: "Tester",
          phoneNumber: "07123 123456",
          emailAddress: "test@lb-dev.io",
          marketDateId: "9",
          stallTypeId: "9",
          clothesRail: true,
          businessName: "TestBusiness",
          businessInsurance: true,
          items: "Testing Stuff"
        }
      ])
    );

I get the following error on running knex seed:run

update or delete on table "markets" violates foreign key constraint "bookings_marketdateid_foreign" on table "bookings"



from Seeding table in KnexJS foreign key constraint

No comments:

Post a Comment