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
