Re: Foreign Key constraints

A copy of this was sent to jdefreitas_at_my-deja.com (if that email address didn't require changing) On Mon, 22 Nov 1999 17:37:27 GMT, you wrote:

>Hi all.
>
>My project leader has decided to disable the foreign key constraing
>property in our Oracle 8.0.5 DB. This means that the value of a primary
>key in the parent table can now be different from the foreign key value
>in the child table. Needless to say, this isn't a Good Thing.
>
>He's asked me to write a script that will basically scan the tables,
>and check referential integrity. I'm a little stumped by this request -
>does anyone have an idea of how this could be done using PL/SQL? Thanks.
>

could we back up -- can we ask why they want to disable them? perhaps we can fix the root of the problem.

finding missing keys is fairly easy (but as soon as you are done, you have to do it all over again).

select *
  from C
 where ( fkey_col1 is not null and fkey_col2 is not null ) -- all fkey colums    and not exists ( select null

                      from p 
                     where pk_col1 = fkey_col1  
                       and pk_col2 = fkey_col2  ... )

or

select fkey_col1, fkey_col2
  from C
 where ( fkey_col1 is not null and fkey_col2 is not null ) -- all fkey colums MINUS
select pk_col1, pk_col2
  from p
/

are two different ways..
>John G. de Freitas
>
>
>Sent via Deja.com http://www.deja.com/
>Before you buy.

--
See http://osi.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Mon Nov 22 1999 - 13:43:38 CST