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 <[email protected]>
To: 'Jonathan Leslie' <[email protected]>; 'General Discussion of SQLite
Database' <[email protected]>
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:[email protected]]
Gesendet: Dienstag, 30. Dezember 2014 15:58
An: [email protected]
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
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
