At 01:20 AM 16/10/2004 +0200, you wrote:

>hello John,
>
>thank you for your reply, but maybe I was not so clear in my message. I'm
>using
>these foreign keys for the sole scope to have cascaded deletes and
>effectively
>some of the foreign tables are only lookup tables. For example:
>
>ALTER TABLE Qso
> ADD CONSTRAINT FK_Qso
> FOREIGN KEY(Log_Owner) REFERENCES Anagraphics(ID_Anagraphic)
> ON DELETE CASCADE;
>
>ALTER TABLE Province_Totals
> ADD CONSTRAINT FK_Province_Totals
> FOREIGN KEY(Log_Owner) REFERENCES Anagraphics(ID_Anagraphic)
> ON DELETE CASCADE;
>
>ALTER TABLE Region_Totals
> ADD CONSTRAINT FK_Region_Totals
> FOREIGN KEY(Log_Owner) REFERENCES Anagraphics(ID_Anagraphic)
> ON DELETE CASCADE;
>
>ALTER TABLE Log_Totals
> ADD CONSTRAINT FK_Log_Totals
> FOREIGN KEY(Log_Owner) REFERENCES Anagraphics(ID_Anagraphic)
> ON DELETE CASCADE;
>
>Doing this with a simple:
>
>DELETE FROM ANAGRAPHICS WHERE ID_ANAGRAPHIC = :SOMEVALUE;
>
>or
>
>DELETE FROM ANAGRAPHICS;
>
>I have deleted from or emptied five tables with only one statement.
>
>But I think that I can achieve the same result with two SP's with separate
>DELETE's statements:
>
>DECLARE PROCEDURE DELETE_ALL
>AS
>BEGIN
> DELETE FROM LOG_TOTALS;
> DELETE FROM REGION_TOTALS;
> DELETE FROM PROVINCE_TOTALS;
> DELETE FROM QSO;
> DELETE FROM ANAGRAPHICS;
>END ^
>
>DECLARE PROCEDURE DELETE_ONE(ID INTEGER)
>AS
>BEGIN
> DELETE FROM LOG_TOTALS WHERE LOG_OWNER = :ID;
> DELETE FROM REGION_TOTALS WHERE LOG_OWNER = :ID;
> DELETE FROM PROVINCE_TOTALS WHERE LOG_OWNER = :ID;
> DELETE FROM QSO WHERE LOG_OWNER = :ID;
> DELETE FROM ANAGRAPHICS WHERE ID_ANAGRAPHIC = :ID;
>END ^
>
>Isn't it? In this SP's example integrity should not be broken because no
>orphan
>records are left after the deletion.

Referential integrity doesn't get broken by deleting the "children" of the
referenced key. You place the action rule (ON DELETE CASCADE) on the
children to signal that, when the parent key gets deleted, the referencing
rows in the "child" table must be deleted also.

It is done by triggers. A trigger is a special kind of stored procedure
that runs each time the specified operation (DELETE, UPDATE, INSERT)
happens to a row on the table. It runs only for that row.

Taking the ON DELETE CASCADE rule: behind that is a trigger on the parent
table that operates the row containing the unique key value that is
referenced by the child. It simply says: "if my row is being deleted,
then first find all of the rows in the referencing table that match my key,
and delete them first."

So, you just need to write a trigger to do that:

CREATE TRIGGER BD_CASC_ANAGRAPHICS FOR ANAGRAPHICS
ACTIVE BEFORE DELETE
AS
BEGIN
DELETE FROM LOG_TOTALS
WHERE LOG_OWNER = OLD.ID_ANAGRAPHIC;
DELETE FROM REGION_TOTALS
WHERE LOG_OWNER = OLD.ID_ANAGRAPHIC;
DELETE FROM PROVINCE_TOTALS
WHERE LOG_OWNER = OLD.ID_ANAGRAPHIC;
DELETE FROM QSO
WHERE LOG_OWNER = OLD.ID_ANAGRAPHIC;
END

Now, you have custom cascades. That is half of the story. You will also
need to take care of the "existence rules". For those, you need two
triggers - one on each child and a corresponding one on the parent, and
some exceptions:
CREATE EXCEPTION NO_ID_ANAGRAPHIC 'Log owner does not exist';
commit;

CREATE TRIGGER ANY_LOG_TOTALS FOR LOG_TOTALS
ACTIVE BEFORE INSERT OR UPDATE
AS
BEGIN
IF (NOT (EXISTS (
SELECT 1 FROM ANAGRAPHICS
WHERE ID_ANAGRAPHIC = NEW.LOG_OWNER))) THEN
EXCEPTION NO_ID_ANAGRAPHIC;
END

Let's say you don't want to allow a cascading update:

CREATE EXCEPTION BAD_CHANGE FOR ANAGRAPHICS
'Can't change Blah because it is in use.';
COMMIT;

CREATE TRIGGER BU_ANAGRAPHICS FOR ANAGRAPHICS
ACTIVE BEFORE UPDATE
AS
BEGIN
IF (
(EXISTS (
SELECT 1 FROM LOG_TOTALS
WHERE LOG_OWNER = OLD.ID_ANAGRAPHIC))
OR (EXISTS (
SELECT 1 FROM REGION_TOTALS
WHERE LOG_OWNER = OLD.ID_ANAGRAPHIC))
OR (EXISTS (
SELECT 1 FROM PROVINCE_TOTALS
WHERE LOG_OWNER = OLD.ID_ANAGRAPHIC))
OR (EXISTS (
SELECT 1 FROM QSO
WHERE LOG_OWNER = OLD.ID_ANAGRAPHIC))
) THEN
EXCEPTION BAD_CHANGE;
END

Or, if you want to cascade updates, write this:

CREATE TRIGGER AU_ANAGRAPHICS FOR ANAGRAPHICS
ACTIVE AFTER UPDATE
AS
BEGIN
UPDATE LOG_TOTALS
SET LOG_OWNER = NEW.ID_ANAGRAPHIC
WHERE LOG_OWNER = OLD.ID_ANAGRAPHIC;
UPDATE PROVINCE_TOTALS
SET LOG_OWNER = NEW.ID_ANAGRAPHIC
WHERE LOG_OWNER = OLD.ID_ANAGRAPHIC;
UPDATE REGION_TOTALS
SET LOG_OWNER = NEW.ID_ANAGRAPHIC
WHERE LOG_OWNER = OLD.ID_ANAGRAPHIC;
UPDATE QSO
SET LOG_OWNER = NEW.ID_ANAGRAPHIC
WHERE LOG_OWNER = OLD.ID_ANAGRAPHIC;
END

You may still want to have indexes on those LOG_OWNER columns in the child
tables. Do this (I'm guessing about the names of the primary keys in the
child tables):

CREATE INDEX FK_LOG_TOTALS_ANAGR
ON LOG_TOTALS(LOG_OWNER, PK_LOG_TOTALS);
CREATE INDEX FK_REGION_TOTALS_ANAGR
ON REGION_TOTALS(LOG_OWNER, PK_REGION_TOTALS);
CREATE INDEX FK_PROVINCE_TOTALS_ANAGR
ON REGION_TOTALS(LOG_OWNER, PK_PROVINCE_TOTALS);
CREATE INDEX FK_QSO_ANAGR
ON REGION_TOTALS(LOG_OWNER, PK_QSO);

This makes the indexing for the trigger searches highly selective. Run
some typical volumes of test data both with and without the indexes, to
test whether the indexing is actually useful for performance.

Apologies for any copy-paste errors - take this code as "indicative". :-)

./heLen