Knex migration settlement fails with foreign key error

Question

I dig my db out of an array that looks like this (words and definitions are in many ways):

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 

This is what I tried:

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

The words are unique in my seeds, but the definitions can be repeated, so I catch a duplication error and grab the duplicate identifier to put this in the join table. This seems like normal, the join table actually ends with 1089 * 5 lines (5445), but I get an error message in 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

Although we cannot see your migrations (and this is a rather old question), what usually happens with these foreign key constraints is that you defined definition_id in words for the definitions.id link. For this reason, you cannot create a word until the definition it refers to exists.

Without testing and without checking for errors, I would suggest that you use something more like this:

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