The usual caveats apply of course -Temporary tables and triggers are only visible by your own connection, so if anyone else came in and made updates then everything would get thrown out of synch and become dangerous. If it's all within a single transaction then it should be ok.
-Since triggers are always for each row, then there would be no such thing as a deferred "temporary fake reference" -Umm, other limitations that my melted end-of-the-day brain can't think of right at the moment. -----Original Message----- From: David Raymond Sent: Thursday, January 05, 2017 3:19 PM To: 'SQLite mailing list' Subject: RE: Quest for "scratch table" implementation in SQLite. Probably not the most elegant solution, but with enough triggers you can simulate close approximations to foreign keys. create table permTable (pk integer primary key, t text); create temp table tempTable (pk int /*references permTable on update cascade on delete set null*/); create temp trigger trg_insert_check before insert on tempTable when new.pk is not null and not exists (select 1 from permTable where pk = new.pk) begin select raise(abort, 'Key constraint 1'); end; create temp trigger trg_update_check before update of pk on tempTable when new.pk is not null and not exists (select 1 from permTable where pk = new.pk) begin select raise(abort, 'Key constraint 2'); end; create temp trigger trg_update_cascade after update of pk on permTable begin update tempTable set pk = new.pk where pk = old.pk; end; create temp trigger trg_delete_null after delete on permTable begin update tempTable set pk = null where pk = old.pk; end; And to test it: insert into permTable values (1, 'one'), (2, 'two'); insert into tempTable values (3); insert into tempTable values (2); select * from tempTable; update tempTable set pk = 3 where pk = 2; update permTable set pk = 3 where pk = 2; select * from tempTable; delete from permTable where pk = 3; select * from tempTable; sqlite> insert into permTable values (1, 'one'), (2, 'two'); sqlite> insert into tempTable values (3); Error: Key constraint 1 sqlite> insert into tempTable values (2); sqlite> select * from tempTable; pk 2 sqlite> update tempTable set pk = 3 where pk = 2; Error: Key constraint 2 sqlite> update permTable set pk = 3 where pk = 2; sqlite> select * from tempTable; pk 3 sqlite> delete from permTable where pk = 3; sqlite> select * from tempTable; pk NULL -----Original Message----- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Smith, Randall Sent: Thursday, January 05, 2017 2:26 PM To: sqlite-users@mailinglists.sqlite.org Subject: [sqlite] Quest for "scratch table" implementation in SQLite. I'm still trying to figure out a graceful way to implement a "scratch" database table that has the following properties: o Can be created on demand while a database connection is open. o Can include references to other material in the database to ensure integrity. o Will be automatically deleted when the database is closed. TEMPORARY tables can't do the second bullet, as I understand it. Right now I have application code that manages such tables, but it seems clunky and error-prone. Am I missing any SQLite feature that would let me do this more easily? Thanks for any suggestions. Randall. _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
