pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
how could a foreign key ever be NULL?
4+ messages / 4 participants
[nested] [flat]

* how could a foreign key ever be NULL?
@ 2001-12-17 11:38 Terrence Brannon <metaperl@mac.com>
  2001-12-17 17:06 ` Re: how could a foreign key ever be NULL? Bruce Momjian <pgman@candle.pha.pa.us>
  2001-12-17 17:24 ` Re: how could a foreign key ever be NULL? Oliver Elphick <olly@lfix.co.uk>
  2001-12-17 20:15 ` Re: how could a foreign key ever be NULL? Stephan Szabo <sszabo@megazone23.bigpanda.com>
  0 siblings, 3 replies; 4+ messages in thread

From: Terrence Brannon @ 2001-12-17 11:38 UTC (permalink / raw)
  To: pgsql-sql

The Momjian book states:

A single-column foreign key is either NULL or matches a primary key

But the problem I have with this statement is that a primary key 
cannot be null or duplicate. And since the foreign key references 
a value that is neither null or duplicate, how could it ever be 
NULL?




^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: how could a foreign key ever be NULL?
  2001-12-17 11:38 how could a foreign key ever be NULL? Terrence Brannon <metaperl@mac.com>
@ 2001-12-17 17:06 ` Bruce Momjian <pgman@candle.pha.pa.us>
  2 siblings, 0 replies; 4+ messages in thread

From: Bruce Momjian @ 2001-12-17 17:06 UTC (permalink / raw)
  To: Terrence Brannon <metaperl@mac.com>; +Cc: pgsql-sql

> The Momjian book states:
> 
> A single-column foreign key is either NULL or matches a primary key
> 
> But the problem I have with this statement is that a primary key 
> cannot be null or duplicate. And since the foreign key references 
> a value that is neither null or duplicate, how could it ever be 
> NULL?

Uh, you could have a foreign key row that doesn't match a primary key
row, and is hence NULL, can't you?

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026



^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: how could a foreign key ever be NULL?
  2001-12-17 11:38 how could a foreign key ever be NULL? Terrence Brannon <metaperl@mac.com>
@ 2001-12-17 17:24 ` Oliver Elphick <olly@lfix.co.uk>
  2 siblings, 0 replies; 4+ messages in thread

From: Oliver Elphick @ 2001-12-17 17:24 UTC (permalink / raw)
  To: Terrence Brannon <metaperl@mac.com>; +Cc: pgsql-sql

On Mon, 2001-12-17 at 11:38, Terrence Brannon wrote:
> The Momjian book states:
> 
> A single-column foreign key is either NULL or matches a primary key
> 
> But the problem I have with this statement is that a primary key 
> cannot be null or duplicate. And since the foreign key references 
> a value that is neither null or duplicate, how could it ever be 
> NULL?

If the foreign key is NULL, it doesn't reference anything.  If this is
not a valid condition, the column should be declared as NOT NULL.

-- 
Oliver Elphick                                Oliver.Elphick@lfix.co.uk
Isle of Wight                              http://www.lfix.co.uk/oliver
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839  932A 614D 4C34 3E1D 0C1C

     "For I say, through the grace given unto me, to every 
      man that is among you: Do not think of yourself more 
      highly than you ought, but rather think of yourself 
      with sober judgement, in accordance with the measure 
      of faith God has given you."            Romans 12:3 

^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: how could a foreign key ever be NULL?
  2001-12-17 11:38 how could a foreign key ever be NULL? Terrence Brannon <metaperl@mac.com>
@ 2001-12-17 20:15 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
  2 siblings, 0 replies; 4+ messages in thread

From: Stephan Szabo @ 2001-12-17 20:15 UTC (permalink / raw)
  To: Terrence Brannon <metaperl@mac.com>; +Cc: pgsql-sql


On Mon, 17 Dec 2001, Terrence Brannon wrote:

> The Momjian book states:
>
> A single-column foreign key is either NULL or matches a primary key
>
> But the problem I have with this statement is that a primary key
> cannot be null or duplicate. And since the foreign key references
> a value that is neither null or duplicate, how could it ever be
> NULL?

First, you don't need to reference a primary key.  Any unique constraint
should work (11.8 referential constraint definition, Syntax rules 2a),
"If the <referenced table and columns> specifies a <reference column
list>, then the set of column names of that <reference column list>
shall be equal to the set of column names in the unique columns of
a unique constraint of the referenced table."  You're probably confusing
it with the behavior that if no column list is given it goes to
the primary key columns (11.8 SR2b I believe).

As for the NULL part, 4.10.2 Table Constraints, (ignoring the not
implemented match partial), "A referential constraint is satisfied
if one of the following conditions is true, depending on the <match
option> specified in the <referntial constraint definition>:
 - If no <match type> was specified then, for each row R1 of the
   referencing table, either at least one of the values of the
   referencing columns in R1 shall be a null value, or the value of
   each referencing column in R1 shall be equal to the value of the
   corresponding referenced column in some row of the referenced
   table.
 -  If MATCH FULL was specified then, for each row R1 of the refer-
    encing table, either the value of every referencing column in R1
    shall be a null value, or the value of every referencing column
    in R1 shall not be null and there shall be some row R2 of the
    referenced table such that the value of each referencing col-
    umn in R1 is equal to the value of the corresponding referenced
    column in R2."





^ permalink  raw  reply  [nested|flat] 4+ messages in thread


end of thread, other threads:[~2001-12-17 20:15 UTC | newest]

Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-12-17 11:38 how could a foreign key ever be NULL? Terrence Brannon <metaperl@mac.com>
2001-12-17 17:06 ` Bruce Momjian <pgman@candle.pha.pa.us>
2001-12-17 17:24 ` Oliver Elphick <olly@lfix.co.uk>
2001-12-17 20:15 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox