Gerbuuun Comments - Answer Overflow

Gerbuuun

Explore posts from servers

NNuxt CDCloudflare Developers DTDrizzle Team

•Created by Gerbuuun on 6/3/2024 in #d1-database

Does `PRAGMA defer_foreign_keys=ON` not

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

12 replies

CDCloudflare Developers

•Created by Gerbuuun on 6/3/2024 in #d1-database

Does `PRAGMA defer_foreign_keys=ON` not

Thanks for looking into it

12 replies

CDCloudflare Developers

•Created by Gerbuuun on 6/3/2024 in #d1-database

Does `PRAGMA defer_foreign_keys=ON` not

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

12 replies

CDCloudflare Developers

•Created by Gerbuuun on 6/3/2024 in #d1-database

Does `PRAGMA defer_foreign_keys=ON` not

I just need a confirmation if this works as intended or that I'm going crazy

12 replies

CDCloudflare Developers

•Created by Gerbuuun on 6/3/2024 in #d1-database

Does `PRAGMA defer_foreign_keys=ON` not

And point 7 of the sqlite docs https://www.sqlite.org/lang_altertable.html

12 replies

CDCloudflare Developers