--- In firebird-support@yahoogroups.com, "Gaurav Sood"
<sood.gaurav@...> wrote:

>
> > > I have a set of cascade delete procedures which delete all

records

> > > associated with clients if you delete the profile of a client

from a

> > > People table within the database. The cascade delete will

totally

> > > remove all details of all clients and their relevant transcripts
> > from
> > > the database if you 'delete from People'.
> >
> > You have just reinvented the wheel.
> >
> > ALTER TABLE CLIENTS ADD CONSTRAINT FK_CLIENTS_1 FOREIGN KEY
> > (PEOPLEID) REFERENCES PEOPLE (ID) ON UPDATE CASCADE ON DELETE

CASCADE;

>
> Hi Adam
>
> So according to this constraint, if I understand correctly, the

table

> Clients which has a foreign key PEOPLEID from table People (with
> primary key ID), shall automatically cascade delete (or update as a
> delete).

The statement

> ALTER TABLE CLIENTS ADD CONSTRAINT FK_CLIENTS_1

Says that the constraint is called FK_CLIENTS_1 (but could be
whatever you like) and is a rule about the data in the clients table.

> FOREIGN KEY (PEOPLEID)

The nature of the constraint is a foreign key, and the field that
contains the value is called peopleid.

> REFERENCES PEOPLE (ID)

The foreign key field is a pointer to the ID field in the People table

>ON UPDATE CASCADE ON DELETE CASCADE;

Cascade any updates to the ID field in the people table to the
peopleID field in the clients table. Cascade any deletes of records
in the people table by removing any records from the clients table
with that peopleID.

It means that if you run:

update people
set ID = ID + 1;

Firebird will automatically update all the records in the clients
table to reflect the new ID, so that the records in the clients table
are still pointing to the same person (who now has a new ID).

If you run:

delete from people
where ID = 5

Firebird will automatically delete any records from the clients table
where PeopleID = 5.

This behaviour is called 'cascade'. There are other behaviours too
that let you set the field to null or raise an exception, these are
all documented, which one to use depends on what you need it to do.

>
> Therefore, I would have to add this constraint to all tables which

use

> the PEOPLE (ID) key as a foreign key.

Yes. It is about preserving consistency. That is, without a foreign
key defined, it would be possible to create a record in the clients
table with a peopleid which does not even exist. (Due to a bug for
example). Rather than letting this cause you grief later on, Firebird
will prevent it from being inserted incorrectly in the first place.

The advantage of a constraint over a trigger is that your triggers
can only work with data your transaction can see. Transaction
isolation could cause that you do not see a people record has been
deleted in another transaction, and you would be allowed to insert a
clients record that pointed to that record. A constraint is
transaction independent. Whilst if you ran a query, you would still
see the record that was deleted, if you tried to add a clients record
to the table that pointed to that deleted record in people, you would
receive an exception. It is also a lot quicker to define and can even
be a part of the create table statement.

Adam