Re: Can anyone advise?????????????

On Fri, 06 Mar 1998 10:58:07 -0500, "A. Elliot Gibbons" <agibbons_at_erols.com> wrote:

>Question:
>
>Is it possible to carry out an update which 'cascades' from
>one table to another as per the 'on delete cascade' used when
>defining foreign keys?
>
>Table Structure:
>
>TABLE_1 (MASTER)
>ID_1 PRIMARY KEY
>
>TABLE_2 (DETAIL)
>ID_1 PRIMARY KEY
>ID_2 PRIMARY KEY
>
>FOREIGN KEY ON TABLE_2.ID_1 REFERENCES TABLE_1.ID_1
>
>For instance, if ID_1 has a value of '999'
>in both TABLE_1 and TABLE_2 can it be modified
>in TABLE_1 and automatically updated in TABLE_2
>without receiving the following error:
>
>'integrity constraint (username.foreign_key_name)
>violated - child record exists'
>
>Any advice would be very much appreciated.
>

You need to remove the foreign key constraint and use triggers on the master table to update / delete records in the child table.

This is generally a bad idea, because it means that the primary key column(s) on your master table do not correspond to a unique identifier, i.e. you have used a unique but not unchanging property of the entity, in which case it is better to use an artificially generated key. Received on Sat Mar 07 1998 - 00:00:00 CST