Re: Deletion and Cascade. The tree dies.

Remove the ON DELETE CASCADE for the constraints deftree_c3 and deftree_c4

HTH Philippe

Joseph Kagume Ngari wrote in message <37717975.D2FC6E58_at_unbc.ca>...
>
>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 Thu Jun 24 1999 - 03:17:24 CDT