> --- In firebird-support@yahoogroups.com, Helen Borrie wrote:
>
> At 10:46 AM 16/01/2008, you wrote:
> >Greets.
> >
> >I have three tables of data, two of which reference the primary key of
> >the third. Basically they are sensors that keep data for a short

period.

> >
> >We have one client that regularly moves the sensors about a building
> >and would prefer to delete/reload the sensor data. The issue is,
> >those two table are not set up to cascade the delete of the sensor to
> >the two data tables, so it is proving to be non-trivial for him.
> >
> >There are views that use (are dependent on) those tables, so dropping
> >the tables and recreating becomes non-trivial as well (for me).
> >
> >The metadata looks like this:
> >CREATE TABLE DATA (
> > MAC VARCHAR(32) NOT NULL,
> > DATETIME_STAMP TIMESTAMP default NULL);
> >
> >/* Foreign key(s) */
> >
> >ALTER TABLE DATA ADD CONSTRAINT FK_DATA_SENSOR_MAC
> > FOREIGN KEY (MAC) REFERENCES SENSOR(MAC)
> > ON UPDATE RESTRICT ON DELETE RESTRICT;
>
> Did you create this constraint in Oracle? It's interesting that
> the DDL engine tolerates it - might be some blast from the past. :-)
> At the moment you have NO ACTION (the default) defined. Other
> options available are CASCADE, SET DEFAULT and SET NULL.

Er...well, I worked with Oracle 9i at HP for 7 years, and some things
just seem to follow me around...

> >Is there an easy ALTER I can do to the FK to cascade my deletes??
>
> No, you can't alter a constraint. Take the database offline, log
> in as the table owner and drop the constraint. Commit that change.
> Then do a fresh ALTER TABLE and redeclare your foreign keys with
> the required actions. Use CASCADE if you want to get rid of the
> child records on delete or update, use SET NULL if you want to
> recycle the children later to a new parent. (The SET NULL action
> won't work obviously if your MAC field in the children is
> non-nullable.) Don't forget to commit again before putting the
> database back online.
>

The data is generally deleted after two days, so orphaned records will
be cleaned up eventually; there is no need to reset the MAC to NULL.
Cascading them (for the short term) would be better, but the MAC is
never changed; if the sensor goes bad we scrap it and get another, so
we can either NO ACTION or CASCADE the UPDATE, and it would have the
same affect.

Which leads me to another question...
Does the DB check that constraint on EVERY update to that table, or
just the updates that affect the constraint. If EVERY update triggers
a check, then NO ACTION would take less clock cycles.

Thanks everyone for the input. I did get it running. but may have to
go back and change it again depending on the answer to the question
above....

Kurt.