Maybe this will work: - declare the foreign keys as ON DELETE CASCADE - register an update hook function - BEGIN - DELETE FROM parent01 WHERE ... (storing the db, table and rowids OUTSIDE SQLite) - ROLLBACK - print whatever you want using the stored info
-----Ursprüngliche Nachricht----- Von: Jonathan Leslie [mailto:j...@jonathanleslie.com] Gesendet: Dienstag, 30. Dezember 2014 16:53 An: General Discussion of SQLite Database Betreff: Re: [sqlite] SQL newbie, how to implement a delete correctly. Sorry, I wasn't clear. what if there are other child tables, say child02- childxx, and you don't know the names of the table, you want to search the entire database? I want a report something like this: for parent01.p01_id == 123, the following records contain references to p01_id == 123: child01 table:child01.scene_gen_cfg_id== 222child01.scene_gen_cfg_id== 432... child02 table:child02.xxx_id = 2432... etc. the idea is without knowing all the names of all the tables, find all references to parent01.p01_id (where value is xxx) From: Hick Gunter <h...@scigames.at> To: 'Jonathan Leslie' <j...@jonathanleslie.com>; 'General Discussion of SQLite Database' <sqlite-users@sqlite.org> Sent: Tuesday, December 30, 2014 10:35 AM Subject: Re: [sqlite] SQL newbie, how to implement a delete correctly. Select * from child01 where p01_id in (select rowid from parent01 where ...); Or Select c.* from parent01 p join child01 c on p.rowid=c.p01_id where ...; -----Ursprüngliche Nachricht----- Von: Jonathan Leslie [mailto:jlesli...@yahoo.com] Gesendet: Dienstag, 30. Dezember 2014 15:58 An: sqlite-users@sqlite.org Betreff: [sqlite] SQL newbie, how to implement a delete correctly. I have a database schema with several tables, and it uses foreign key relationships. I'm trying to figure out a good implementation for deleting a record. As we know simply removing a record that is used as a foreign key in another table have serious implications. The first thing I'd like is an sql script that displays the effected records of a source record. For example If I select a single record from a table I want to search the entire database for references to that record. how ca I do that? Here is the sample DB I'm using: a parent table has no foreign constraints, a child table has foreign constraints, but is not inherited by any other table, and a parentchild table has both foreign constraints, and is inherited by some other table. deleting a record from the child01 record is not a problem, as it is not referenced in any other table. 1) deleting a record from parent01 is not as straightforward. parent01 records are used in the table child01. I would like a script that I could run that would show what records that WOULD be effected should I delete a particular record of parent01 2) I imagine there will be some follow up questions, but lets see where question (1) gets me and if I can figure out stuff from there. TIA, Jleslie48 -- start of database -----------------------CREATE TABLE parent01 ( p01_id integer NOT NULL PRIMARY KEY, description text, low_lim real, upper_lim real, enable_atm boolean, pc01_id integer); CREATE TABLE child01 ( scene_gen_cfg_id integer NOT NULL PRIMARY KEY, description text, target_model_file char(64), p01_id integer, p04_id integer, po2_id integer, pc01_id integer, FLITES_cfg_file text, /* Foreign keys */ CONSTRAINT p02_id_fk FOREIGN KEY (po2_id) REFERENCES parent02(po2_id), FOREIGN KEY (p04_id) REFERENCES parent04(p04_id), FOREIGN KEY (p01_id) REFERENCES parent01(p01_id), CONSTRAINT pc01_id_fk FOREIGN KEY (pc01_id) REFERENCES parentchild01(pc01_id)); CREATE TABLE parent02 ( po2_id integer NOT NULL PRIMARY KEY, description text, curve_shader_src text); CREATE TABLE parentchild01 ( pc01_id integer NOT NULL PRIMARY KEY, description text, p03_id integer, /* Foreign keys */ CONSTRAINT p03_id_fk FOREIGN KEY (p03_id) REFERENCES parent03_nodes(p03_id)); CREATE TABLE parent03_nodes ( p03_id integer NOT NULL PRIMARY KEY, description text, parent_node_id integer, name char(64), scale_x real, scale_y real, scale_z real); CREATE TABLE parent04 ( p04_id integer NOT NULL PRIMARY KEY, description text, vertical_fov real, horizontal_fov real); -- end of database ----------------------- _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
