Thread: Foreign keys and null

Hi,

I have a table which contains information about MRTG graphs.
Each of the graphs belongs to a device (switch, router, host)
or to a service (web server, news server etc.), so I made a
second table containing those devices and services, and used
foreign keys in the graphs table to point into the devices
table.  So far so good.

We also have a "component table" which contains information
about hardware componets (switches, routers etc.).  So I had
the idea that I could make a link between the MRTG devices
table and the component table, i.e. put a foreign key in the
devices table pointing into the components table.

However -- The devices table also contains services which are
not in the components table (because they aren't hardware
components, but they are regarded as "virtual devices").

Normally the column should be null in those cases, but a
foreign key cannot be null, as far as I know.

Any hints or ideas?  I'm probably missing some clue here,
or maybe my tables could be organized in a better way.

Regards
   Oliver

--
Oliver Fromme, secnetix GmbH & Co KG, Oettingenstr. 2, 80538 München
Any opinions expressed in this message may be personal to the author
and may not necessarily reflect the opinions of secnetix in any way.

Perl is worse than Python because people wanted it worse.
        -- Larry Wall

Uhm, sorry for the noise, but the problem solved itself,
kind of ...

I wasn't aware that a foreign key actually _can_ be null.

The docs weren't very specific about that.  Thinking that
a foreign key must contain a valid reference to another
table (after all, that's what a foreign key is for), I
implied that it cannot be null -- which was wrong.

Thanks for listening anyway.  :-)

Regards
   Oliver

--
Oliver Fromme, secnetix GmbH & Co KG, Oettingenstr. 2, 80538 München
Any opinions expressed in this message may be personal to the author
and may not necessarily reflect the opinions of secnetix in any way.

With Perl you can manipulate text, interact with programs, talk over
networks, drive Web pages, perform arbitrary precision arithmetic,
and write programs that look like Snoopy swearing.

Oliver Fromme <olli@lurza.secnetix.de> writes:
> I wasn't aware that a foreign key actually _can_ be null.

> The docs weren't very specific about that.  Thinking that
> a foreign key must contain a valid reference to another
> table (after all, that's what a foreign key is for), I
> implied that it cannot be null -- which was wrong.

If you put a NOT NULL constraint on the column along with the FOREIGN
KEY constraint, then every entry must be a valid reference.  However,
in cases where you want some of the rows to have no reference, you
leave off the NOT NULL.  Simple eh?

Also, if you are using multi-column foreign keys, there is a MATCH
option that determines the semantics of partially-null key sets.
But that might be getting far afield for the novice list...

            regards, tom lane