CODEWITHSUNDEEP
javascriptpostgresqlknex.js
lnamba
lnamba

Reputation: 1721

Postgres/Knex "insert or update on table "locations" violates foreign key constraint"

I am continuing to get the same error when I try to run seeds in Knex and Postgres. The error is error: insert or update on table "locations" violates foreign key constraint "locations_user_id_fore ign". Can anyone figure out why it is throwing this error? I have tried changing a bunch of things. Thanks!

User Migrations

exports.up = function(knex, Promise) {
  return knex.schema.createTable('users', function(table) {
    table.increments()
    table.string('email').notNullable();
    table.string('password').notNullable();
  })
};

exports.down = function(knex, Promise) {
  return knex.schema.dropTable('users')
};

Locations Table

exports.up = function(knex, Promise) {
  return knex.schema.createTable('locations', function(table) {
    table.increments('id');
    table.string('placename').notNullable();
    table.float('latitude').notNullable();
    table.float('longitude').notNullable();
    table.integer('user_id').notNullable().references('id').inTable('users').onDelete('cascade');
  })
};

exports.down = function(knex, Promise) {
  return knex.schema.dropTable('locations')
};

User Seeds

exports.seed = function(knex, Promise) {
  // Deletes ALL existing entries
  return knex('users').del()
    .then(function () {
      // Inserts seed entries
      return knex('users').insert([
        {email: '[email protected]', password: 'p1'},
        {email: '[email protected]', password: 'p2'},
        {email: '[email protected]', password: 'p3'}
      ]);
    });
};

Location Seeds

exports.seed = function(knex, Promise) {
  // Deletes ALL existing entries
  return knex('locations').del()
    .then(function () {
      // Inserts seed entries
      return knex('locations').insert([
        {placename: 'Himeji', latitude: 34.8394, longitude: 134.6939, user_id: 1},
        {placename: 'Long Beach', latitude: 33.7701, longitude: 118.1937, user_id: 3},
        {placename: 'Seattle', latitude: 47.6253, longitude: 122.3222, user_id: 2}
      ]);
    });
};

Upvotes: 4

Views: 7282

Answers (1)

Utsav
Utsav

Reputation: 8143

Putting as answer, as it would be too big for a comment.

Well the error message is clear enough that you have violated a foreign key constraint.

This is because for inserting a row in locations you need to give a user_id as well, which is referencing to column id in table users. If that particular user is not present in users table, you cannot insert that user_id in locations. See this line 

table.integer('user_id').notNullable().references('id').inTable('users').onDelete('cascade');

So first you have to add that user in users table, then you can insert it it location.

Upvotes: 8

Related Questions