KnexJs - Sqlite3 删除级联不起作用 [英] KnexJs - Sqlite3 On Delete cascade doesn't work

问题描述

我正在尝试使 onDelete('CASCADE') 在 knex js 上工作，但它似乎根本不起作用.这是我的 knexjs 迁移:

i'm trying to make onDelete('CASCADE') work on knex js but it doesn't seem to work at all. Here's my knexjs migrations :

const tableName = 'users'

exports.up = function(knex) {
  return knex.schema.createTable(tableName, table => {
      table.increments('id').primary();
      table.string('username');
      table.string('email');
      table.string('password');
      table.timestamps(true,true);
  })
};

exports.down = function(knex) {
  knex.schema.dropTableIfExists(tableName);
};

const tableName = 'todolists';

exports.up = function(knex) {
  return knex.schema.createTable(tableName, table => {
      table.increments('id').primary();
      table.string('title');
      table.boolean('completed').defaultTo(false);
      table.timestamps(true,true);

      table.integer('users_id').unsigned().notNullable();
      table.foreign('users_id').references('users.id').onDelete('CASCADE');
  })
};

exports.down = function(knex) {
  return knex.schema.dropTableIfExists(tableName);
};

当我尝试删除 users 表时，他们各自的 todolist 不会被删除，并且仍然引用已删除用户的 ID.

When i try to delete users table, their respective todolists aren't removed and still reference the deleted user's ids.

这是 Sqlite3 CLI 架构定义，似乎 onDelete('CASCADE') 已开启.

Here's the Sqlite3 CLI schema definition and it seems the onDelete('CASCADE') is ON.

sqlite> .schema users
CREATE TABLE `users` (`id` integer not null primary key autoincrement, `username` varchar(255), `email` varchar(255), `password` varchar(255), `created_at` datetime not null default CURRENT_TIMESTAMP, `updated_at` datetime not null default CURRENT_TIMESTAMP);

sqlite> .schema todolists
CREATE TABLE `todolists` (`id` integer not null primary key autoincrement, `title` varchar(255), `completed` boolean default '0', `created_at` datetime not null default CURRENT_TIMESTAMP, `updated_at` datetime not null default CURRENT_TIMESTAMP, `users_id` integer not null, foreign key(`users_id`) references `users`(`id`) on delete CASCADE);

我错过了什么吗?

感谢您的帮助.

原来我需要首先在 Sqlite 端支持外键(这是一个 SQlite 相关问题，与 knex/objection js 无关)我在初始化 knex js 后就在我的程序中运行了这行代码:

EDIT : Turns out i needed to first foreign key support on the Sqlite side (it's an SQlite related issue, not related to knex/objection js) I ran this line code in my program just after initializing knex js :

await knex.raw('PRAGMA foreign_keys = ON');

这将启用外键支持并考虑到我的架构定义中的删除级联"约束.

This enables foreign key support and takes into account the "on delete CASCADE" constraint in my Schema definition.

推荐答案

我正在尝试使 onDelete('CASCADE') 在 knex js 上工作，但它似乎根本不起作用.这是我的 knexjs 迁移:

i'm trying to make onDelete('CASCADE') work on knex js but it doesn't seem to work at all. Here's my knexjs migrations :

const tableName = 'users'

exports.up = function(knex) {
  return knex.schema.createTable(tableName, table => {
      table.increments('id').primary();
      table.string('username');
      table.string('email');
      table.string('password');
      table.timestamps(true,true);
  })
};

exports.down = function(knex) {
  knex.schema.dropTableIfExists(tableName);
};

const tableName = 'todolists';

exports.up = function(knex) {
  return knex.schema.createTable(tableName, table => {
      table.increments('id').primary();
      table.string('title');
      table.boolean('completed').defaultTo(false);
      table.timestamps(true,true);

      table.integer('users_id').unsigned().notNullable();
      table.foreign('users_id').references('users.id').onDelete('CASCADE');
  })
};

exports.down = function(knex) {
  return knex.schema.dropTableIfExists(tableName);
};

当我尝试删除 users 表时，他们各自的 todolist 不会被删除，并且仍然引用已删除用户的 ID.

When i try to delete users table, their respective todolists aren't removed and still reference the deleted user's ids.

这是 Sqlite3 CLI 架构定义，似乎 onDelete('CASCADE') 已开启.

Here's the Sqlite3 CLI schema definition and it seems the onDelete('CASCADE') is ON.

sqlite> .schema users
CREATE TABLE `users` (`id` integer not null primary key autoincrement, `username` varchar(255), `email` varchar(255), `password` varchar(255), `created_at` datetime not null default CURRENT_TIMESTAMP, `updated_at` datetime not null default CURRENT_TIMESTAMP);

sqlite> .schema todolists
CREATE TABLE `todolists` (`id` integer not null primary key autoincrement, `title` varchar(255), `completed` boolean default '0', `created_at` datetime not null default CURRENT_TIMESTAMP, `updated_at` datetime not null default CURRENT_TIMESTAMP, `users_id` integer not null, foreign key(`users_id`) references `users`(`id`) on delete CASCADE);

我错过了什么吗?

感谢您的帮助.

解决方案:原来我需要首先在 Sqlite 端支持外键(这是一个 SQlite 相关问题，与 knex/objection js 无关)我在初始化 knex js 后就在我的程序中运行了这行代码:

SOLUTION : Turns out i needed to first foreign key support on the Sqlite side (it's an SQlite related issue, not related to knex/objection js) I ran this line code in my program just after initializing knex js :

await knex.raw('PRAGMA foreign_keys = ON');

这将启用外键支持并考虑到我的架构定义中的删除级联"约束.

This enables foreign key support and takes into account the "on delete CASCADE" constraint in my Schema definition.

这篇关于KnexJs - Sqlite3 删除级联不起作用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！