Re: Foreign Keys

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
>