Re: Problems with NULL-values in foreign key Fields?

d.foerderer_at_zi.unizh.ch (Daniel Foerderer) writes:

> I had a problem with the manager of my group because he said that I
> have to avoid foreign key fields which can be Null in database design.
> In my books about database design they write that null values in
> foreign key fields are ok.
>
> Here is the example where the problem rose from:
>
> I have too tables:
>
> 1. table ip_adresses(ip_adress_id,......) with PK ip_adress_id
>
> 2. table ip_names(ip_name_id, ip_adress_id, rl_ip_adress_id,.....)
> with PK ip_name_id
>
> Table ip_names has the
> foreign keys ip_adress_id and rl_ip_adress_id which point both to
> the PK ip_adress_id of the table ip_adresses.
> I introduced the second foreign key rl_ip_adress_id to mark the
> reverse-lookup-ip-name of an ip-adress.
> The foreign key field rl_ip_adress_id can be null because not every
> ip-name has to be a reverse-lookup ip-name.
>
> For me this design is perfectly all right. If there is a problem where
> is it??
>
> Daniel Foerderer

Your manager is correct.

NULL Foreign Keys are _legal_ but they are definitely not _ok_. They lead to problems and mistakes when joining on the nullable column. They are usually an indication of a poor design.

In your case, a better design would possibly be along these lines:

table ip_addresses( ip_address_id ) PK( ip_address_id )

table ip_names( ip_name_id ) PK( ip_name_id )

table ip_address_names( ip_address_id, ip_name_id, ip_name_direction ) PK( ip_address_id, ip_name_id, ip_name_direction ) CHECK( ip_name_direction IN ( 'FORWARD', 'REVERSE' ) ) FKs are the obvious ones.

You can create views to "denormalize" into your format if necessary.

-- 

Daryl Richter
Lead Programmer, Exelon Power Team

http://www.exeloncorp.com/powerteam