CODEWITHSUNDEEP
javascriptnode.jspostgresqlknex.js
Peter Pik
Peter Pik

Reputation: 11193

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

Upvotes: 2

Views: 1497

Answers (1)

redneb
redneb

Reputation: 23850

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).

Upvotes: 3

Related Questions