Cloudflare Developers•5mo ago

Does `PRAGMA defer_foreign_keys=ON` not

Does PRAGMA defer_foreign_keys=ON not disable on update and on delete actions? Kinda weird when SQLite often requires recreating tables (and thus triggering those actions)

8 Replies

Gerbuuun•5mo ago

@Max (@rozenmd) Can you clarify? I'm really stuck on this one.

Max (@rozenmd)•5mo ago

@Gerbuuun can you explain what you're trying to do + provide a repro? I don't follow

Gerbuuun•5mo ago

This issue describes it very well https://github.com/cloudflare/workers-sdk/issues/5438

GitHub

🚀 Feature Request: Allow turning off foreign key check while migrat...

Some table altering tasks in SQLite cannot be achieved without replacing it with a new table (by dropping - recreating the table) (like adding foreign keys, changing primary keys, updating column t...

Gerbuuun•5mo ago

And point 7 of the sqlite docs https://www.sqlite.org/lang_altertable.html I just need a confirmation if this works as intended or that I'm going crazy

Max (@rozenmd)•5mo ago

@geelen would know, but he's out of office until Tuesday also I don't see on update or on delete in your example in the github issue unclear what

Rows are still deleted or set to null.

refers to - which rows, in which table?

Gerbuuun•5mo ago

Lets say you have a user and a post table. Where posts are related to a user.

CREATE TABLE `user` (
  `id` integer PRIMARY KEY AUTOINCREMENT NOT NULL,
  `name` text NOT NULL
);

CREATE TABLE `post` (
  `id` integer PRIMARY KEY AUTOINCREMENT NOT NULL,
  `user_id` integer NOT NULL,
  FOREIGN KEY (`user_id`) REFERENCES `user`(`id`) ON DELETE cascade
);

CREATE TABLE `user` (
  `id` integer PRIMARY KEY AUTOINCREMENT NOT NULL,
  `name` text NOT NULL
);

CREATE TABLE `post` (
  `id` integer PRIMARY KEY AUTOINCREMENT NOT NULL,
  `user_id` integer NOT NULL,
  FOREIGN KEY (`user_id`) REFERENCES `user`(`id`) ON DELETE cascade
);

If I want to for example change the name column in the user table to have a default value I need to recreate the table due to sqlite limitations:

CREATE TABLE `new_user` (
  `id` integer PRIMARY KEY AUTOINCREMENT NOT NULL,
  `name` text NOT NULL DEFAULT 'No name given'
);

INSERT INTO `new_user` (`id`, `name`)
SELECT `id`, `name`
FROM `user`;

DROP TABLE `user`;
ALTER TABLE `new_user` RENAME TO `user`;

CREATE TABLE `new_user` (
  `id` integer PRIMARY KEY AUTOINCREMENT NOT NULL,
  `name` text NOT NULL DEFAULT 'No name given'
);

INSERT INTO `new_user` (`id`, `name`)
SELECT `id`, `name`
FROM `user`;

DROP TABLE `user`;
ALTER TABLE `new_user` RENAME TO `user`;

(this procedure is described in the sqlite docs) But due to the ON DELETE cascade defined foreign key in the post table, all posts referenced to any user are deleted (because we deleted the old table but created a new one with the same data). PRAGMA foreign_keys=OFF; or PRAGMA defer_foreign_keys=ON; are supposed to disable this behavior right? But even if I use those pragma statements the cascading still happens Thanks for looking into it

Max (@rozenmd)•5mo ago

Looked into this today, PRAGMA legacy_alter_table=ON was supposed to have fixed this: https://github.com/cloudflare/workerd/pull/2063 Can't seem to make the example in the PR work, though - will investigate further

GitHub

sqlite: allow PRAGMA legacy_alter_table by smerritt · Pull Request ...

There's certain kinds of schema changes that are really painful to do in SQLite; this makes things a little easier. For example, let's say you want to change the "users.email" col...

Gerbuuun•5mo ago

Seems a bit weird to me. The SQLite docs state PRAGMA legacy_alter_table=OFF is the default and they document the migration procedure without any mention of turning on legacy_alter_table for it to work

Gaming

Programming

Does `PRAGMA defer_foreign_keys=ON` not