Cloudflare Developers•2y ago

You need to share more about what you're doing here. Specifically, see: * ON CASCADE UPDATE/DELETE:

You need to share more about what you're doing here. Specifically, see:

ON CASCADE UPDATE/DELETE: https://www.sqlite.org/foreignkeys.html#fk_actions
How foreign keys interact with
```
DROP TABLE
```
DROP TABLE
```
DROP TABLE
```
DROP TABLE
commands: https://www.sqlite.org/foreignkeys.html#fk_schemacommands

MMatt Silverlock You need to share more about what you're doing here. Specifically, see: * ON CAS...

Tony Leung•3/28/24, 4:46 PM

Sorry

I forgot to mention my scenario:

Table A has column b_idb_id which reference table B column idid, ON DELETE CASCADE ON UPDATE CASCADEON DELETE CASCADE ON UPDATE CASCADE

In my migration file, I'm trying to do a workaround like this:

PRAGMA foreign_keys = OFF;
create table b_temp ...;
drop table b...;
alter table b_tmp rename to b;
PRAGMA foreign_keys = ON;

PRAGMA foreign_keys = OFF;
create table b_temp ...;
drop table b...;
alter table b_tmp rename to b;
PRAGMA foreign_keys = ON;

but all of my records in table A is cascaded

TTony Leung Sorry 😦 I forgot to mention my scenario: - Table A has column `b_id` which refe...

Matt SilverlockOP•3/28/24, 4:50 PM

It's not clear to me that disabling fkeys works (and you can't do that in D1 anyway):

If foreign key constraints are enabled when it is prepared, the DROP TABLE command performs an implicit DELETE to remove all rows from the table before dropping it. The implicit DELETE does not cause any SQL triggers to fire, but may invoke foreign key actions or constraint violations. If an immediate foreign key constraint is violated, the DROP TABLE statement fails and the table is not dropped. If a deferred foreign key constraint is violated, then an error is reported when the user attempts to commit the transaction if the foreign key constraint violations still exist at that point. Any "foreign key mismatch" errors encountered as part of an implicit DELETE are ignored.

Try

PRAGMA defer_foreign_keys = on

PRAGMA defer_foreign_keys = on

(I have not tested this) - although I expect it will still fail as you still have violated fkey constraints when the transaction goes to complete.

Original message was deleted

Max (@rozenmd)•3/28/24, 4:57 PM

delete and recreate is probably the cheapest

MMatt Silverlock It's not clear to me that disabling fkeys works (and you can't do that in D1 any...

Tony Leung•3/28/24, 4:58 PM

yeah it does not work either

Tony Leung•3/28/24, 4:59 PM

there is an issue which i found which has the same problem as me: https://github.com/launchbadge/sqlx/issues/2085

GitHub

Can't do SQLite migrations involving temporarily disabling foreign ...

I'm trying to do a migration involving adding a constraint to a field within a table that is referenced as a foreign key by other tables in SQLite. As you might know, SQLite's ALTER syntax ...

Tony Leung•3/28/24, 5:00 PM

if it does not work then I guess I have to create a temp table to store data from the child table, which is not ideal for large table

Tony Leung•3/28/24, 5:00 PM

i guess modifying the table on the fly is not the best practice for sqlite

TTony Leung there is an issue which i found which has the same problem as me: https://github...

Matt SilverlockOP•3/28/24, 5:02 PM

The second comment says: https://github.com/launchbadge/sqlx/issues/2085#issuecomment-1237474188

follow up: it seems like bracketing it with PRAGMA defer_foreign_keys = ON; and PRAGMA defer_foreign_keys = OFF; does make it work. I am not familiar enough with arcane SQLite stuff to give a super informed view on whether this should be automatically applied to every migration, but I suspect it might be a good idea - my understanding is that the foreign key constraints will still be checked, but just at the time that the transaction is committed.

MMatt Silverlock The second comment says: https://github.com/launchbadge/sqlx/issues/2085#issueco...

Tony Leung•3/28/24, 5:06 PM

I've tried bracketing with PRAGMA defer_foreign_keys = ONPRAGMA defer_foreign_keys = ON/PRAGMA defer_foreign_keys = OFFPRAGMA defer_foreign_keys = OFF but it does not work either...

Tony Leung•3/28/24, 5:08 PM

do i still need PRAGMA foreign_keys = OFF;?

TTony Leung I've tried bracketing with `PRAGMA defer_foreign_keys = ON`/`PRAGMA defer_foreig...

Matt SilverlockOP•3/28/24, 5:08 PM

What does “not work” mean - it does nothing? Errors?

Tony Leung•3/28/24, 5:10 PM

I'm sorry, I mean after bracketing the migration with PRAGMA defer_foreign_keysPRAGMA defer_foreign_keys, Table B was modified and my table A are cascaded without any errors like before

JoeDoe•3/28/24, 5:16 PM

Is there a visual explorer for local databases?

JoeDoe•3/28/24, 5:17 PM

something like d1-manager

JJoeDoe Is there a visual explorer for local databases?

Tony Leung•3/28/24, 5:17 PM

the database is stored under .wrangler/state/v3/d1.wrangler/state/v3/d1. It's a sqlite file, you could explore or even modify it using your favorite tool

Tony Leung•3/28/24, 5:18 PM

I'm using sqlite viewer extension in vscode (which does not allows modification)

JoeDoe•3/28/24, 5:19 PM

ah ok perfect thank you

Tony Leung•3/28/24, 5:19 PM

you could use any more advanced tool like datagrip

JoeDoe•3/28/24, 5:19 PM

Ill find something in intellij

JoeDoe•3/28/24, 5:19 PM

some plugin

Tony Leung•3/28/24, 5:19 PM

IntelliJ or any Jetbrains IDE has database plugin bundled

Tony Leung•3/28/24, 5:20 PM

it's a mini version of datagrip

JJoeDoe some plugin

Tony Leung•3/28/24, 5:21 PM

Here, if you use intelliJ ultimate then it's bundled
https://www.jetbrains.com/help/idea/relational-databases.html#first-steps

JoeDoe•3/28/24, 5:21 PM

The Database Tools and SQL plugin is available only in IntelliJ IDEA Ultimate.

The Database Tools and SQL plugin is available only in IntelliJ IDEA Ultimate.

JoeDoe•3/28/24, 5:21 PM

Tony Leung•3/28/24, 5:22 PM

you could use dbeaver, it's opensource

JoeDoe•3/28/24, 5:22 PM

there some plugins Ill try

MMatt Silverlock What does “not work” mean - it does nothing? Errors?

Tony Leung•3/28/24, 5:28 PM

is there any better solution rather than creating a temp table to hold my data since I'm having multiple tables referencing table B and they holds a lot of records. I've tried another solution as mentioned in: https://github.com/launchbadge/sqlx/issues/2085#issuecomment-1499859906 but it raises an error like this:

✘ [ERROR] Wrangler could not process the provided SQL file, as it contains several transactions.

  D1 runs your SQL in a transaction for you.
  Please export an SQL file from your SQLite database and try again.

✘ [ERROR] Wrangler could not process the provided SQL file, as it contains several transactions.

  D1 runs your SQL in a transaction for you.
  Please export an SQL file from your SQLite database and try again.

JJoeDoe ```The Database Tools and SQL plugin is available only in IntelliJ IDEA Ultimate...

Isaac McFadyen•3/28/24, 5:31 PM

This is quite incorrect, I can confirm that the bundled SQLite plugin is available in all Jetbrains IDEs. Not sure why they have this listed in their docs.

IIsaac McFadyen This is quite incorrect, I can confirm that the bundled SQLite plugin is availab...

Tony Leung•3/28/24, 5:33 PM

that misleading line is noted in IntelliJ document.

TTony Leung that misleading line is noted in IntelliJ document.

Isaac McFadyen•3/28/24, 5:34 PM

Yeah I know. I'm unsure why it's noted in there.

Isaac McFadyen•3/28/24, 5:34 PM

The Datagrip integration is available in all IDEs so not sure why they noted it.

Isaac McFadyen•3/28/24, 5:34 PM

Maybe it's for a more advanced version? As far as I know they're all identical though...

IIsaac McFadyen Yeah I know. I'm unsure why it's noted in there.

Tony Leung•3/28/24, 5:36 PM

to boost intelliJ selling I guess? A sql console is more than enough for casual use to me. Maybe the more advanced database like dynamo, snowflake is not supported?

Isaac McFadyen•3/28/24, 5:36 PM

Possibly? Unsure.

Max (@rozenmd)•3/28/24, 5:44 PM

Early release, but if you want to try an unoptimized