answer in a way I can make sense out of. I know I should get this, but
so far no way...
I'm creating tables and doing queries in Perl, and Nulls have started
to bother me greatly. The first issue is, as far as I understand it, a
column should be NOT NULL if it is necessary (required) data.
Now, if a column doesn't have to be NOT NULL; that is, it's not
_required_ data, but is useful in some way like a descriptive comment,
that specifier is left off. That's easy..unless I'm going stupid.
Then when we get to the default value specifier, my ears leak.
According to the reference manual...
mysql> INSERT INTO my_table (phone) VALUES (NULL);
mysql> INSERT INTO my_table (phone) VALUES ('');
Both statements insert a value into the phone column, but the first
inserts a NULL value and the second inserts an empty string. The
meaning of the first can be regarded as ``phone number is not known''
and the meaning of the second can be regarded as ``the person is known
to have no phone, and thus no phone number.''
Should I interpret the first statement as "the phone value is an
optional field, and is set to NULL, to indicate that we don't have one
yet?" Likewise, if we don't have a descriptive comment for an item, we
simply enter NULL to indicate that. And the second statement means that
there's nothing there, and that's the value that's supposed to be
there.[ It's this second statement that impies a "null" to me. This
seems backwards.]
Now in addition to this, there's the issue of setting the default value
as NULL. Obviously this has to be a column that is allowed to be NULL,
and is therefore not required. In my case, let's say I have a phony
table like this
CREATE TABLE `artist` (
`id` int(11) NOT NULL auto_increment,
`lastName` varchar(30) NOT NULL default 'Unknown',
`firstName` varchar(20) NOT NULL default 'Unknown',
`nickname` varchar(20) default NULL,
PRIMARY KEY (`id`)
) TYPE=MyISAM
Obviously the id and last and first names I want, and the nickname is
optional. The default values for the first & last names are not really
relevant, right, because when I INSERT the record I need a real value
for those, right? I really don't even need defaults, is that correct?
But for the optional field, I may or may not have a value. Now in that
case, the values are very often NULL, so I'll have a column filled with
NULLs. Not only is this a pain in Perl, but it seems to contradict the
reference manual's description. I'd also like to be able to search in
this column, to see if there's a match. So it seems like I've got this
wrong.
Thanks. And thanks for being patient!
--
"It's beyond my ken... and my Barbie, and all of my action figures."