Peter Pik Peter Pik - 2 months ago 17
Node.js Question

column violate foreign key constraints

I'm trying to create a relationship between two tables, however i seem to get, some issues when i try to insert my object to postgres database. i keep getting following error:

insert or update on table "tournament" violates foreign key constraint "tournament_league_id_foreign"
. which i guess is related to my knex syntax?

Insert into db

var data = {
id: id,
name: name,
league_id: leagueId
};

var query = knex('tournament').insert(data).toString();
query += ' on conflict (id) do update set ' + knex.raw('name = ?, updated_at = now()',[name]);

knex.raw(query).catch(function(error) {
log.error(error);
})


Knex tables

knex.schema.createTable('league', function(table) {
table.increments('id').primary();
table.string('slug').unique();
table.string('name');
table.timestamp('created_at').notNullable().defaultTo(knex.raw('now()'));
table.timestamp('updated_at').notNullable().defaultTo(knex.raw('now()'));
}),
knex.schema.createTable('tournament', function(table) {
table.string('id').primary();
table.integer('league_id').unsigned().references('id').inTable('league');
table.string('name');
table.boolean('resolved');
table.timestamp('created_at').notNullable().defaultTo(knex.raw('now()'));
table.timestamp('updated_at').notNullable().defaultTo(knex.raw('now()'));
})

Answer

When you created the tournament table, for the column league_id you you specified that .references('id').inTable('league'). This means that for every row in that table, there must exist a row in the table league whose id is the same as the value of the league_id field in the former row. Apparently in your insert (is this your only insert?) you are adding a row to tournament whose league_id does not exist in league. As a rule, the foreign constraint (i.e. the .references part) implies that you must create the league first and then tournaments in that league (which actually makes sense).

Comments