Default value for Foreign Key column, NULL or 0?

[Devy]

This might be a trivial question. And I didn't find much of the
discussion after googling the topic. But I am still debating of
picking a default value for foreign key column to represent the non-
existence of such fk.

Is there any advantage between using NULL vs. using 0? What are the
pros and cons? What do you guys use as a convention?

Will using 0s reducing table size in terms of storage?

Thanks!!

[Peter H. Coffin]

On Tue, 3 Nov 2009 14:36:51 -0800 (PST), Devy wrote:
> This might be a trivial question. And I didn't find much of the
> discussion after googling the topic. But I am still debating of
> picking a default value for foreign key column to represent the non-
> existence of such fk.
>
> Is there any advantage between using NULL vs. using 0? What are the
> pros and cons? What do you guys use as a convention?

Null's an *automatic* special case, that contaminate the specialness
to other operations automatically. 0 you have to code a lot of checking
for yourself. For example, null records cannot possibly join to
anything. A zero foreign key joins to any record that HAPPENS to end up
with a zero value in the referenced field, and if you default THAT to
zero as well, well... let's just say it's REALLY EASY to end up with a
lot of records that match that you don't want to.

> Will using 0s reducing table size in terms of storage?

Not by any appreciable amount. Disk is *cheap*.

--
62. I will design fortress hallways with no alcoves or protruding structural
supports which intruders could use for cover in a firefight.
--Peter Anspach's list of things to do as an Evil Overlord

[toby]

On Nov 3, 5:36 pm, Devy <dev...@gmail.com> wrote:
> This might be a trivial question. And I didn't find much of the
> discussion after googling the topic. But I am still debating of
> picking a default value for foreign key column to represent the non-
> existence of such fk.

You should use NULL.
--------------------

For longer answer, see Peter's. :)