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.

>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.

./heLen