Обсуждение: RI and NULL's

One more question:

    I'm planning to create generic trigger procs for PK/FK stuff.
    So that it's simply insert/delete the appropriate  pg_trigger
    entries during CREATE/ALTER table.

    Assuming  NULL's  are  allowed  in FK values (are they?), I'd
    like to know what the correct handling of NULL values is.  If
    an  attribute  of the FK has the NULL value, must a PK with a
    NULL  in  the  corresponding  attribute  exist  or  is   this
    attribute  completely  left  out  of  the WHERE clause in the
    check?

    Other way round -  NULL  value  in  attribute  of  referenced
    table.   What  to  delete  from  FK  in the case of ON DELETE
    CASCADE?


Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#========================================= wieck@debis.com (Jan Wieck) #

At 17:07 23/09/99 +0200, you wrote:
>
>    Assuming  NULL's  are  allowed in FK values (are they?)

I don't think they should be since two null fields are not equal, and the
reason for FK constraints to to require the foregn record exists. Also, I'm
pretty sure PK values should not be null.

>    like to know what the correct handling of NULL values is.  If
>    an  attribute  of the FK has the NULL value, must a PK with a
>    NULL  in  the  corresponding  attribute  exist  or  is   this
>    attribute  completely  left  out  of  the WHERE clause in the
>    check?

I don't think so. I believe PK values can't be null, so no FK field should
be null.

>    Other way round -  NULL  value  in  attribute  of  referenced
>    table.   What  to  delete  from  FK  in the case of ON DELETE
>    CASCADE?

This problem goes away.


----------------------------------------------------------------
Philip Warner                    |     __---_____
Albatross Consulting Pty. Ltd.   |----/       -  \
(A.C.N. 008 659 498)             |          /(@)   ______---_
Tel: +61-03-5367 7422            |                 _________  \
Fax: +61-03-5367 7430            |                 ___________ |
Http://www.rhyme.com.au          |                /           \|                                |    --________--
PGP key available upon request,  |  /
and from pgp5.ai.mit.edu:11371   |/