Re: Foreign Keys

In article <7hhe3f$ftq$1_at_paxfeed.eni.net>,   "Chumley" <mschumle_at_willscot.com> wrote:
> Has anyone had an experience where foreign keys on tables "magically"
are
> removed. I have been running some archiving/purge procedures from my
> development team. Since then I have noticed that several foreign
keys have
> been removed, which in turn cause data integrity errors. I have
verified
> that the procedures do not use any ALTER statements to drop the
foreign
> keys.
>
> Can cursors and deletes cause the foreign keys to be removed?
>
>

Do you really mean the Primary key gets removed? Example: table AA defines the primary key:

create table AA (
ID number primary key,
NAME varchar2(50)
);

then insert some data so you have:
ID NAME
 1 Chumley
 2 Prochak

THen the child table with the foriegn key:

create table BB (
ID number deferences AA(ID),
GIVEN varchar2(40)
);

Put some data in this BB table

ID GIVEN
 1 Mark
 2 Ed
 2 John

If you then delete Prochak from the first table, the rows Ed and John still exist. The constraint is only checked on Inserts. IF you want these child records to be deleted, then you need to include the ON DELETE CASCADE phrase in the definition, like this:

create table BB (
ID number deferences AA(ID) on delete cascade, GIVEN varchar2(40)
);

This might be the answer to the question that you asked. The foriegn key records are removed automatically when you have the cascade option. So if you delete Prochak from AA in this case, then Ed and John also are removed.

Does that help?

--
Ed Prochak
Magic Interface, Ltd.
ORACLE services
440-498-3702

--== Sent via Deja.com http://www.deja.com/ ==-- ---Share what you know. Learn what you don't.--- Received on Fri May 14 1999 - 11:56:14 CDT