Foreign Keys

[Thomas LeBlanc]

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

[Ben]

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

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

[Bruno Wolff III]

On Fri, Nov 21, 2003 at 14:35:54 -0600,
Thomas LeBlanc <th*********@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.

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

[Stephan Szabo]

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.

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org