CODEWITHSUNDEEP
node.jsnode-sqlite3knex.js
Florian3007
Florian3007

Reputation: 97

Foreign Key Constraint ignored by Knex.js

I am trying to set up Knex.js in conjunction with express and Node.js.

I am using SQLite3 for Knex.js.

Currently I have the problem that Knex creates the foreign keys correctly with the tables, but simply ignores them when inserting (In an external DB browser I can see that the foreign keys are set and there I can't add anything with non-existing foreign key either). But I want that I can't insert tuples with knex, don't satisfy the foreig key constraint.

Here is how to create the tables:

knex.schema.hasTable('User').then(res => {
  if (res)
    return;
  knex.schema.createTable('User', function (table) {
    table.increments('id').primary();
    table.string('username').notNullable().unique();
  }).then(res => {
    console.log("Table User Created");
  });
});

knex.schema.hasTable('Room').then(res => {
  if (res)
    return;
  knex.schema.createTable('Room', function (table) {
    table.increments('id').primary();
    table.string('room_name').notNullable().unique();
    table.integer('owner_id').unsigned().notNullable();

    table.foreign('owner_id').references('User.id');
    // table.foreign('owner_id').references('id').inTable('User'); //Not Working too
  }).then(res => {
    console.log("Table Room Created");
  });
});

Here the provisional adding of the test data.

knex('user').insert({
    username: 'f',
    mail: '[email protected]'
}).then(res => {
    console.log(res);
});

knex('room').insert({
    room_name: 'r',
    owner_id: 2  // THIS SHOULD NOT WORK!!!
}).then(res => {
    console.log(res);
});

Upvotes: 0

Views: 1831

Answers (1)

Florian3007
Florian3007

Reputation: 97

I finally figured it out: SQLite disabled foreign key constraints by default.

Adding this solves the Problem

knex.raw("PRAGMA foreign_keys = ON;").then(() => {
    console.log("Foreign Key Check activated.");
});

Upvotes: 4

Related Questions