CODEWITHSUNDEEP
sqlsqliteexpressknex.jsobjection.js
J. Munson
J. Munson

Reputation: 2875

SQLite: Foreign Key "ON DELETE SET NULL" action not getting triggered

Why is ON DELETE SET NULL failing when deleting a row via the application code, but it behaves correctly when manually executing an SQL statement?

I have a todo table and a category table. The todo table has a category_id foreign key that references id in the category table, and it was created with the "ON DELETE SET NULL" action.

create table `category` (
  `id` integer not null primary key autoincrement,
  `name` varchar(255) not null
);

create table `todo` (
  `id` integer not null primary key autoincrement,
  `title` varchar(255) not null,
  `complete` boolean not null default '0',
  `category_id` integer,
  foreign key(`category_id`) references `category`(`id`) on delete SET NULL on update CASCADE
);

I also have an endpoint in my application that allows users to delete a category.

categoryRouter.delete('/:id', async (req, res) => {
  const { id } = req.params
  await req.context.models.Category.delete(id)
  return res.status(204).json()
})

This route successfully deletes categories, but the problem is that related todo items are not getting their category_id property set to null, so they end up with a category id that no longer exists. Strangely though, if I open up my database GUI and manually execute the query to delete a category... DELETE FROM category WHERE id=1... the "ON DELETE SET NULL" hook is successfully firing. Any todo item that had category_id=1 is now set to null.

Full application source can be found here.

Upvotes: 2

Views: 2892

Answers (2)

hata
hata

Reputation: 12478

FYI and other people who stumbled across a similar problem, you need PRAGMA foreign_keys = ON not only for the child table but also for the parent table.

When I set PRAGMA foreign_keys = ON only for a program which handles the child table, ON UPDATE CASCADE was enabled but ON DELETE SET NULL was still disabled. At last I found out that I forgot PRAGMA foreign_keys = ON for another program which handles the parent table.

Upvotes: 0

J. Munson
J. Munson

Reputation: 2875

Figured it out, thanks to MikeT.

So apparently SQLite by default has foreign key support turned off. WTF!

To enable FKs, I had to change my code from this...

const knex = Knex(knexConfig.development)
Model.knex(knex)

to this...

const knex = Knex(knexConfig.development)
knex.client.pool.on('createSuccess', (eventId, resource) => {
  resource.run('PRAGMA foreign_keys = ON', () => {})
})
Model.knex(knex)

Alternatively, I could have done this inside of the knexfile.js...

module.exports = {
 development: {
   client: 'sqlite3',
   connection: {
     filename: './db.sqlite3'
   },
   pool: {
     afterCreate: (conn, cb) => {
       conn.run('PRAGMA foreign_keys = ON', cb)
     }
   }
 },
 staging: {},
 production: {}
}

Upvotes: 5

Related Questions