Knex 迁移种子设定完成，但出现外键错误

Question

我正在从一个看起来像这样的数组中播种我的数据库（单词和定义处于多对多关系中）：

var seeds = [
{
    "word": "Click",
    "definitions": ["Computer", "Mouse", "Tasto", "Pulsante", "Selezionare"]
}, {
    "word": "Galoppo",
    "definitions": ["Cavallo", "Andatura", "Trotto", "Ippica", "Passo"]
}, {
    "word": "Raggio",
    "definitions": ["Sole", "Bicicletta", "Diametro", "Luce", "Laser"]
}, {
.
.
.goes on for 1089 objects

这是我尝试过的：

exports.seed = function (knex, Promise) {
var promises = seeds.map(function (seed) {
    return knex('words').insert({
        word: seed.word
    }, 'id').then(function (word_id) {
        var promises = seed.definitions.map(function (definition) {
            return knex('definitions').insert({
                definition: definition
            }, 'id').catch(function (err) {
                if (err.code === 1062)
                    return knex('definitions').select('id').where({
                        definition: definition
                    }).then(function (duplicate) {
                        return knex('definitions_words').insert({
                            definition_id: duplicate[0].id,
                            word_id: word_id
                        });
                    });
            }).then(function (definition_id) {
                return knex('definitions_words').insert({
                    definition_id: definition_id,
                    word_id: word_id
                });
            });
        });
        return Promise.all(promises);
    });
});
return Promise.all(promises);
};

单词在我的种子中是唯一的，但定义可能会重复，所以我捕获了重复错误并获取重复的 id 以将其放入连接表中。它似乎工作正常，连接表实际上以 1089*5 行 （5445） 结束，但我在 cli 上收到错误：

Error: Cannot add or update a child row: a foreign key constraint fails 
(`mytable`.`definitions_words`,
CONSTRAINT `definitions_words_definition_id_foreign`
FOREIGN KEY (`definition_id`) REFERENCES `definitions` (`id`))

Answer 1

虽然我们看不到您的迁移（这是一个相当古老的问题），但这些外键限制通常发生的事情是您在words中定义了definition_id以引用definitions.id。因此，您无法在word引用definition存在之前创建。

如果不对其进行测试，并且几乎没有错误检查，我会想象您使用更像这样的东西：

exports.seed = function (knex, Promise) {
  var promises = seeds.map(function (seed) {
    // Check for an existing definition. More recently
    // you can use `whereNotExists` but you always need
    // an id here whatever the case
    return knex('definitions')
      .select('id')
      .where('definition', seed.definition)
      .then(function (definition_id) {
        if (definition_id.length === 1) return definition_id[0]
        return knex('definitions')
          .insert({ definition: definition })
      })
      .then(function (definition_id) {
        // Use the definition once it exists
        return knex('words')
          .insert({ word: seed.word, definition_id: definition_id })
          .then(function (word_id) {
            return { word_id: word_id, definition_id: definition_id }
          });
      })
      .then(function (join_ids) {
        // Finally, update the join table
        return knex('definitions_words')
          .insert({
            definition_id: join_ids.definition_id,
            word_id: join_ids.word_id
          })
      })
  return Promise.all(promises);
};