[BUG]:ON DELETE cascade not working about drizzle-orm

[richardpasquiou]

Did you try to set client.pragma('foreign_keys = ON'); before creating your dbClient = drizzle(client, { schema })

[faribauc]

@richardpasquiou Wow, that was a rookie mistake 🤦 😆

Thanks for pointing that out (the error, not the "rookie" part 😃)! That being fixed, it's still not working. Here's more details:

I'm adding a migration where I add a foreign key on a table

CREATE TABLE `elevations-2` (
	`id` integer PRIMARY KEY AUTOINCREMENT NOT NULL,
	`group` text NOT NULL,
	`description` text NOT NULL,
	`vcg` real NOT NULL,
	`is_default` integer NOT NULL,
	`file_id` integer,
	FOREIGN KEY (`file_id`) REFERENCES `file-attachments`(`id`) ON UPDATE no action ON DELETE cascade
);
--> statement-breakpoint
INSERT INTO `elevations-2` SELECT * FROM `elevations`;--> statement-breakpoint
DROP TABLE `elevations`;

Drizzle migration tool generated the first and last SQL commands and I added the data copy in-between.

The cascading delete doesn't seem to work even when I manually delete a row from elevations in my SQL editor (dBeaver).

PRAGMA foreign_keys;
PRAGMA foreign_keys = OFF; /* to show the difference between on and off)*/
PRAGMA foreign_keys;
PRAGMA foreign_keys = ON;
PRAGMA foreign_keys;
DELETE FROM "elevations-2"  WHERE id=6;

The file-attachments entry still exists.

Is there something wrong with the new table?

[richardpasquiou]

elevations-2 is your baby table. file-attachments is your mama table. You can't drop the baby and recursively, delete the mama entry.
It works in one way : drop the mama entry, and it will cascade on baby table

[faribauc]

@richardpasquiou Oh ok, I see. The relation field should be on file-attachments with an elevation_id field instead of having the file_id field on elevations. I was hoping not to have to do that as file-attachments links to multiple tables for a variety of entities. I guess I'll have to rethink my schema. Maybe with pivot tables... Thanks for the insight!