Null values

[Jake]

Recently I've been told most table fields should not be Null Allowed. Does
this mean Required property set to Yes, Zero Length String not allowed, or a
Default value specifiied?
How do I make a field not Null Allowed?
Thanks!

 

[TC]

Recently I've been told most table fields should not be Null Allowed.

Maybe they should - maybe they shouldn't! It's decided entirely on a
case by case basis. There are few if any cases where you would ever
say: "There are too many nullable fields, we'd better remove some", or
"There are not enough nullable fields, we'd better add some".

You define a field as nullable, if and only if it is acceptable for
that field to have no value. For example, in an employee database, you
might have a Hobby field. If you want that *every* employee *must have*
a hobby entry, with *no exceptions*, you make that field a Required
(ie. non nullable) field. But if you want he hobby entry to be
optional, you make it a nullable field (Required=no). The same with
every other field - you choose the setting, field by field.

Does this mean Required property set to Yes

Yes. Required=yes means not nullable, can't be blank, must be present.
Required=no means nullable, can be blank, can be absent.

Zero Length String

If this is yes, you can store an empty string ("") in the field. This
is a bad idea for various reasons. You should generally set this to No,
not allowed.

Default value specifiied?

That just says whether th field is given a default (initial) value
automatically, when you create a new record.

How do I make a field not Null Allowed?

Set Required=yes.

HTH,
TC (MVP Access)
http://tc2.atspace.com

 

[Allen Browne]

Jake, while I agree with TC's comments, here is a more opinionated view:
- Allow nulls unless you have a reason not to.
- Never allow zero-length strings until you have a special reason to.

For an explanation of nulls, see:
Nulls: Do I need them?
at:
http://allenbrowne.com/casu-11.html

Just this week, I had to work with a database where the developer clearly
did not understand nulls and had set every field as required. There were
bunches of non-normalized fields as well, so we had around a dozen
unnecessary required fields. The task we were working on took 5 hours
instead of 1. (Read as 5 times the cost.) You create a maintenance nightmare
if you store unnormalized values and make all fields required.

Zero-length-strings (ZLS) are a different story. If you permit them, you
will have to consider whether to check for both nulls and ZLSs in every
query, expression, or code you write. (Remember, you still have nulls as
well, regardless of the Required property, e.g. outer-join queries.) This
means it is more error-prone, slower to execute, slower to develop, and
harder to maintain, for no benefit.

 

[TC]

Jake, while I agree with TC's comments, here is a more opinionated view:

- Allow nulls unless you have a reason not to.

We're saying the same thing, IMHO. "Allow nulls unless you have a
reason not to", is the same as: "only make fields required, when you
have to" - my position exactly

Cheers,
TC (MVP Access)
http://tc2.atspace.com
(off for a breather)

 

[TC]

I see your point now. I said to use nullable "if and only if ...". I
guess that makes it sound like nullable is the rare case - not the
common one. Your words have the opposite emphasis: nullable is the
common case - which is probably true.

Cheers,
TC

 

[Jake]

Thanks very much TC and Allen Browne for your insights!
Cheers! Jake