List:General Discussion« Previous MessageNext Message »
From:Thimble Smith Date:September 30 1999 2:47am
Subject:Re: Database planning and design
View as plain text  
At 15:16, 19990929, Andreas Fiesser wrote:
>Patrick,
>> Something like this:
>> 
>> CREATE TABLE customer (
>> customer_id INT NOT NULL DEFAULT '0' PRIMARY KEY,
>> first_name VARCHAR(80) NOT NULL DEFAULT 'None',
>> [ ... more example table ... ]

Andreas, you are asking good questions.

>Why do you put all fields "not null" and then declare a default ?
>I'd rather had a PRIMARY KEY and let the other fields be filled as the
>user likes them to be. 

A typical way of creating examples like this is to get the table the
way you want it using the 'mysql' program, and then once you've tested
your examples you use 'mysqldump -d' to get the CREATE TABLE statements
back out.  'mysqldump' uses the above format.

>Why do you define the prim. key "NOT NULL DEFAULT '0'" ?

In MySQL before 3.23, keys had to be declared NOT NULL (you could not
make an index on a column that allowed NULL values).  The default for
any numeric column is 0 unless it is specified explicitly.

>Doesn't "DEFAULT '0'" contradict "NOT NULL" ?

No, not at all!  NULL is not the same as 0 in any way.  NULL is a special
value, 0 is a normal integer.  The mailing list archives have lots of
discussions about this difference.

>Isn't "PRIMARY KEY" implicit for "NOT NULL" and being an index ?

No, not quite.  Even if you specify that a field is the PRIMARY KEY,
you still need to specify that it is NOT NULL.  MySQL doesn't take
care of that for you (it would be too surprising to the developer if
they didn't realize what had happened).  You must let MySQL know that
*you* know there can't be NULL values stored in that field.  But you
are right that specifying PRIMARY KEY does create an index (a UNIQUE
index, in fact) on that field.

>Btw. is the last assumption right at all ? Is there an index created
>implicitly for the primary key field(s) ?

Yep!

>I'd really like to know if there is some consideration where your scheme
>is better than the simple way I'd go.

His scheme used DEFAULT 'None' for all of the CHAR fields, instead of the
implicit DEFAULT ''.  This is up to you, the developer.  I would prefer to
keep DEFAULT '' for most of those fields.  What if someone has a first name
of 'None'?  Of course that's not going to happen, but that's the kind of
question you would ask when making these decisions.  Let's say you're going
to print a list of names.  You might get:

    Thimble R. Smith
    None None Fiesser
    Patrick None None

I think that is ugly; I'd rather see:

    Thimble R. Smith
    Fiesser
    Patrick

But it's all in how you code your applications, what kinds of queries you
do, etc.

There are many reasons to avoid using NULL.  But if NULL is what you really
mean, it's often best to use it.  But you should really know what NULL is
all about before you think of allowing it into your tables.

Tim
Thread
Database planning and designChris W.28 Sep
  • Re: Database planning and designThimble Smith29 Sep
  • What is the BEST way to Unsubscribe if mailing to mysql-unsubscribe@lists.mysql.com failedMichael Lim Shek Sia29 Sep
  • What is the BEST way to Unsubscribe if mailing to mysql-unsubscribe@lists.mysql.com failedMichael Lim Shek Sia29 Sep
  • What is the BEST way to Unsubscribe if mailing to mysql-unsubscribe@lists.mysql.com failedMichael Lim Shek Sia29 Sep
  • What is the BEST way to Unsubscribe if mailing to mysql-unsubscribe@lists.mysql.com failedMichael Lim Shek Sia29 Sep
  • What is the BEST way to Unsubscribe if mailing to mysql-unsubscribe@lists.mysql.com failedMichael Lim Shek Sia29 Sep
  • Re: Database planning and designAndreas Fiesser29 Sep
  • Re: Database planning and designBill Gerrard29 Sep
    • TO:Bill Gerrard Database planning and designScott Perkins29 Sep
  • Re: Database planning and designPatrick Greenwell29 Sep
    • Re: Database planning and designAndreas Fiesser30 Sep
      • Re: Database planning and designThimble Smith30 Sep
    • Re: Database planning and designAndreas Fiesser30 Sep
  • Re: What is the BEST way to Unsubscribe if mailing to mysql-unsubscribe@lists.mysql.com failedPeter Dresslar29 Sep