Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Deletion and Cascade. The tree dies.
Hi and thanks for reading
The table below is meant to represent a tree. HOwever, when i try and
delete a record, it deletes all the records. I think it has to do with
the flagrant use of ON DELETE CASCADE.
Can you see any way for me to correct this problem.
If a sibling is deleted, nothing else should be deleted.
if a parent is deleted, all its children should be deleted.
Thank you
Please respond to ngari_at_unbc.ca
The table and its contraints are below
CREATE TABLE deftree (
project INTEGER NOT NULL,
version INTEGER NOT NULL,
id INTEGER NOT NULL,
parent INTEGER,
child INTEGER,
sibling INTEGER,
entry INTEGER NOT NULL,
source INTEGER,
sversion INTEGER,
isnew INTEGER
);
ALTER TABLE deftree ADD CONSTRAINT deftree_c1 PRIMARY KEY(project, version, id);
ALTER TABLE deftree ADD CONSTRAINT deftree_c2
FOREIGN KEY(project, version, parent)
REFERENCES deftree(project, version, id)
ON DELETE CASCADE;
ALTER TABLE deftree ADD CONSTRAINT deftree_c3
FOREIGN KEY(project, version, child)
REFERENCES deftree(project, version, id)
ON DELETE CASCADE;
ALTER TABLE deftree ADD CONSTRAINT deftree_c4
FOREIGN KEY(project, version, sibling)
REFERENCES deftree(project, version, id)
ON DELETE CASCADE;
ALTER TABLE deftree ADD CONSTRAINT deftree_c5
FOREIGN KEY(project, entry)
REFERENCES deflist(project, id)
ON DELETE CASCADE;
ALTER TABLE deftree ADD CONSTRAINT deftree_c6
FOREIGN KEY(project, sversion) REFERENCES versions(project, id)
ON DELETE CASCADE;
ALTER TABLE deftree ADD CONSTRAINT deftree_c7
FOREIGN KEY(project, sversion, source)
REFERENCES deftree(project, version, id)
ON DELETE CASCADE;
Received on Wed Jun 23 1999 - 19:19:01 CDT