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
>