javascript - KnexJS 外键约束中的种子表

Question

我正在构建一个 NodeJS API 后端并使用 Knex 来处理数据库迁移等。

我有 3 个表:stalls、markets 和 bookings

在我的 bookings 表中，我想引用 stalls 和 markets 表中的 id 列。

我已按如下方式设置我的预订迁移:-

    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");
};

摊位:

    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");
};

市场:

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");
};

我已经用一些虚拟数据为 stalls 和 markets 表播种，但是当我尝试使用下面的种子为 bookings 表播种时文件:

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"
        }
      ])
    );

运行 knex seed:run 时出现以下错误

表“markets”的更新或删除违反了表“bookings”的外键约束“bookings_marketdateid_foreign”

Answer 1

bookings 表的 marketDateId 字段被定义为 foreign key引用其他表中的字段。这是为了确保参照完整性。看到这个相关postgres tutorial page举个例子。

因此，您的代码必须确保它只尝试分配存在于另一个表的行中的 marketDateId 值。