Postgres Pro
Downloads
Home   >   mailing lists

Thread: Foreign Keys

Foreign Keys

From
"Thomas LeBlanc"
Date:
I am able to enter a null in a foreign key field in one table(table2)
without the foreign key relationship (table1.t1_id) validating the value.
Why is this?

Below are 2 table's scripts:

create table table1 (
    t1_id         VARCHAR(32) not null,
    t1_desc         VARCHAR(32)      null,
constraint table1_pkey primary key (t1_id) )  ;


create table table2 (
    t2_id        VARCHAR(32) not null,
    t1_id         VARCHAR(32) null,
                t2_desc                     VARCHAR(32) null,
  constraint table2_pkey primary key (t2_id) )  ;

create index table2_t1_id
    on table2 ( t1_id )  ;

alter table table2
    add constraint table1_table2_t1_id_FK1
    foreign key ( t1_id )
    references table1 ( t1_id )
    ON DELETE RESTRICT ON UPDATE CASCADE ;

_________________________________________________________________
Is there a gadget-lover on your gift list?  MSN Shopping has lined up some
good bets!  http://shopping.msn.com

Re: Foreign Keys

From
Ben
Date:
My understanding of forign keys is that the constraint is only checked
when the values are non-null. I'm not aware of any way to say "keep the
value non-null unless there is a null in the column referenced."

On Fri, 21 Nov 2003, Thomas LeBlanc wrote:

> I am able to enter a null in a foreign key field in one table(table2)
> without the foreign key relationship (table1.t1_id) validating the value.
> Why is this?
>
> Below are 2 table's scripts:
>
> create table table1 (
>     t1_id         VARCHAR(32) not null,
>     t1_desc         VARCHAR(32)      null,
> constraint table1_pkey primary key (t1_id) )  ;
>
>
> create table table2 (
>     t2_id        VARCHAR(32) not null,
>     t1_id         VARCHAR(32) null,
>                 t2_desc                     VARCHAR(32) null,
>   constraint table2_pkey primary key (t2_id) )  ;
>
> create index table2_t1_id
>     on table2 ( t1_id )  ;
>
> alter table table2
>     add constraint table1_table2_t1_id_FK1
>     foreign key ( t1_id )
>     references table1 ( t1_id )
>     ON DELETE RESTRICT ON UPDATE CASCADE ;
>
> _________________________________________________________________
> Is there a gadget-lover on your gift list?  MSN Shopping has lined up some
> good bets!  http://shopping.msn.com
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 7: don't forget to increase your free space map settings
>

Re: Foreign Keys

From
Bruno Wolff III
Date:
On Fri, Nov 21, 2003 at 14:35:54 -0600,
  Thomas LeBlanc <thomasatiem@hotmail.com> wrote:
> I am able to enter a null in a foreign key field in one table(table2)
> without the foreign key relationship (table1.t1_id) validating the value.
> Why is this?

That is how foreign keys work. If you don't want null values for the
foreign key, use a not null constraint in addition to the foreign
key constraint.

You shouldn't be using null key values in the referenced table.

Re: Foreign Keys

From
Stephan Szabo
Date:
On Fri, 21 Nov 2003, Thomas LeBlanc wrote:

> I am able to enter a null in a foreign key field in one table(table2)
> without the foreign key relationship (table1.t1_id) validating the value.
> Why is this?

To go into more detail from the previous answers:
 For a single column key, a NULL value is always allowed in the
referencing table (table2) and isn't considered to be referencing
any row in table1.
 For a multiple column key, how NULLs are handled depends on the match
type specified.  For the unspecified match type/MATCH SIMPLE, if any field
in the referencing table is NULL, the constraint is considered satisified
and doesn't reference any particular row in table1. For MATCH FULL, either
all the columns must be NULL for a row (and the constraint is satisified
and there is no referenced row) or none of the columns may be NULL and
the check is done normally.  For MATCH PARTIAL (which we don't implement),
if all columns are NULL the constraint is satisified and there is no
referenced row, if all columns are non-NULL the check is done normally,
if some columns are NULL and others are non-NULL, the non-NULL columns
are checked against the referenced table without concern for matching the
columns where the referencing row has a NULL.