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
