When to default Number fields to 0?

[Mark]

I have just been looking at a database where all number fields are set to
default to 0 at table level. I know this is the default setting but I
previously I have either always set number fields to default to null or a
quantity/primary key value if applicable. Is there any advantage in setting
number fields to default to 0? I can imagine it being used when a quantity
is genuinely 0 e.g. RejectedQty = 0. Should foreign keys ever default to 0?

Thanks

 

[John Vinson]

I can imagine it being used when a quantity
is genuinely 0 e.g. RejectedQty = 0.

It should only default to 0 if that makes sense for the data
represented by the field.

Should foreign keys ever default to 0?

Certainly not (especially since there will almost never be a related
table record with 0 in the primary key).

It was a rather stupid design decision on Microsoft's part to make all
number fields default to 0, and it's a real annoyance for linking
fields.

John W. Vinson[MVP]

 

[Guest]

Defaulting to 0 has some advantages. For example some calculations and
functions will fail with a null value. For example if you try to add + 1 to a
record with a null value, you'll get a Null back. With the default of 0
you'll return 1.

Of course if you divide something by 0, you get an error. If you divide by
Null, you return a Null.

Should foriegn keys ever default to 0? Only if you have a record 0 in the
parent table. Otherwise you are going to have orphan records in the child
table.