Re: [racket-users] Trouble with "on delete cascade" using sqlite
Thanks, that solved it! -Philip On Tue, Apr 18, 2017 at 4:54 PM, David Storrswrote: > IIRC, SQLite defaults to PRAGMA foreign_keys = OFF when you open a > database. Without FKs being on there is nothing for the CASCADE to trigger > on. You'll need to turn the FKs on before it will work. > > Just add this line before the first 'insert into tUsers' and then it will > work: > > (query-exec db "PRAGMA foreign_keys = ON") > > > On Tue, Apr 18, 2017 at 5:33 PM, Philip McGrath > wrote: > >> The following program returns '(#("demo" "j...@example.com")), whereas I >> think it should return '(), because I expect deleting the row from "tUsers" >> to delete the corresponding row in "tEmail". Is this a bug in the db >> library (or elsewhere, or am I doing something wrong)? >> >> #lang at-exp racket >> >> (require db) >> >> (define db >> (sqlite3-connect #:database 'memory)) >> >> (query-exec db >> @~a{ >> CREATE TABLE tUsers ( >> userUUID TEXT NOT NULL, >> userName TEXT NOT NULL, >> PRIMARY KEY (userUUID) >> ) >> }) >> >> (query-exec db >> @~a{ >> CREATE TABLE tEmail ( >> userUUID TEXT NOT NULL, >> userEmail TEXT NOT NULL, >> PRIMARY KEY (userUUID,userEmail), >> FOREIGN KEY (userUUID) REFERENCES tUsers >> ON DELETE CASCADE ON UPDATE CASCADE >> ) >> }) >> >> (query-exec db >> "INSERT INTO tUsers (userUUID,userName) VALUES (?,?)" >> "demo" >> "Jane Doe") >> >> (query-exec db >> "INSERT INTO tEmail (userUUID,userEmail) VALUES (?,?)" >> "demo" >> "j...@example.com") >> >> (query-exec db >> "DELETE FROM tUsers WHERE userUUID = ?" >> "demo") >> >> (query-rows db "SELECT * FROM tEmail") >> >> -- >> You received this message because you are subscribed to the Google Groups >> "Racket Users" group. >> To unsubscribe from this group and stop receiving emails from it, send an >> email to racket-users+unsubscr...@googlegroups.com. >> For more options, visit https://groups.google.com/d/optout. >> > > -- You received this message because you are subscribed to the Google Groups "Racket Users" group. To unsubscribe from this group and stop receiving emails from it, send an email to racket-users+unsubscr...@googlegroups.com. For more options, visit https://groups.google.com/d/optout.
Re: [racket-users] Trouble with "on delete cascade" using sqlite
IIRC, SQLite defaults to PRAGMA foreign_keys = OFF when you open a database. Without FKs being on there is nothing for the CASCADE to trigger on. You'll need to turn the FKs on before it will work. Just add this line before the first 'insert into tUsers' and then it will work: (query-exec db "PRAGMA foreign_keys = ON") On Tue, Apr 18, 2017 at 5:33 PM, Philip McGrathwrote: > The following program returns '(#("demo" "j...@example.com")), whereas I > think it should return '(), because I expect deleting the row from "tUsers" > to delete the corresponding row in "tEmail". Is this a bug in the db > library (or elsewhere, or am I doing something wrong)? > > #lang at-exp racket > > (require db) > > (define db > (sqlite3-connect #:database 'memory)) > > (query-exec db > @~a{ > CREATE TABLE tUsers ( > userUUID TEXT NOT NULL, > userName TEXT NOT NULL, > PRIMARY KEY (userUUID) > ) > }) > > (query-exec db > @~a{ > CREATE TABLE tEmail ( > userUUID TEXT NOT NULL, > userEmail TEXT NOT NULL, > PRIMARY KEY (userUUID,userEmail), > FOREIGN KEY (userUUID) REFERENCES tUsers > ON DELETE CASCADE ON UPDATE CASCADE > ) > }) > > (query-exec db > "INSERT INTO tUsers (userUUID,userName) VALUES (?,?)" > "demo" > "Jane Doe") > > (query-exec db > "INSERT INTO tEmail (userUUID,userEmail) VALUES (?,?)" > "demo" > "j...@example.com") > > (query-exec db > "DELETE FROM tUsers WHERE userUUID = ?" > "demo") > > (query-rows db "SELECT * FROM tEmail") > > -- > You received this message because you are subscribed to the Google Groups > "Racket Users" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to racket-users+unsubscr...@googlegroups.com. > For more options, visit https://groups.google.com/d/optout. > -- You received this message because you are subscribed to the Google Groups "Racket Users" group. To unsubscribe from this group and stop receiving emails from it, send an email to racket-users+unsubscr...@googlegroups.com. For more options, visit https://groups.google.com/d/optout.
[racket-users] Trouble with "on delete cascade" using sqlite
The following program returns '(#("demo" "j...@example.com")), whereas I think it should return '(), because I expect deleting the row from "tUsers" to delete the corresponding row in "tEmail". Is this a bug in the db library (or elsewhere, or am I doing something wrong)? #lang at-exp racket (require db) (define db (sqlite3-connect #:database 'memory)) (query-exec db @~a{ CREATE TABLE tUsers ( userUUID TEXT NOT NULL, userName TEXT NOT NULL, PRIMARY KEY (userUUID) ) }) (query-exec db @~a{ CREATE TABLE tEmail ( userUUID TEXT NOT NULL, userEmail TEXT NOT NULL, PRIMARY KEY (userUUID,userEmail), FOREIGN KEY (userUUID) REFERENCES tUsers ON DELETE CASCADE ON UPDATE CASCADE ) }) (query-exec db "INSERT INTO tUsers (userUUID,userName) VALUES (?,?)" "demo" "Jane Doe") (query-exec db "INSERT INTO tEmail (userUUID,userEmail) VALUES (?,?)" "demo" "j...@example.com") (query-exec db "DELETE FROM tUsers WHERE userUUID = ?" "demo") (query-rows db "SELECT * FROM tEmail") -- You received this message because you are subscribed to the Google Groups "Racket Users" group. To unsubscribe from this group and stop receiving emails from it, send an email to racket-users+unsubscr...@googlegroups.com. For more options, visit https://groups.google.com/d/optout.