Great! Thanks for the refresher. Definitely going to use more
constraints in future DB development.

Cheers
Gaurav

On 5/12/06, Adam <s3057043@...> wrote:
> --- 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
>
>
>
>
>
>
>
>
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
> Visit http://firebird.sourceforge.net and click the Resources item
> on the main (top) menu. Try Knowledgebase and FAQ links !
>
> Also search the knowledgebases at http://www.ibphoenix.com
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
> Yahoo! Groups Links
>
>
>
>
>
>
>
>


--



Analyst
B. Engineering, B. IT
A.N.U (2004)

M: +61 401 409 620
H: +61 3 9376 8580


"In life, always do right. This will gratify some people and astonish the rest."

— Mark Twain