Pages

Thursday, February 10, 2011

NO_AUTO_VALUE_ON_ZERO

The annoying thing about MYSQL is that, by default, it's autoincrement program triggers on zero. IMHO, it should only trigger on Null.

If the column Id is set to AUTO_INCREMENT in Table Data_Table the command:

INSERT INTO Data_Table (id, firstcolumn) 
VALUES (0,'zero');

will insert a row (#, 'zero') where # is the next number in the auto insert sequence.

Because the MySQL auto increment triggers on 0, copying a table from one computer to another changes the zero row.

In my import to the new database, the system changed some of my zero rows and caused me some headaches.

Of course, these headache were nothing like the one I get when talking to Set Theorists.

On more than one occasion I've been shouted down by Set Theory Nazis dedicated to the cause of including zero in the definition of the set of Natural Numbers. Set Theorists want to include zero in sets so that they become a group. I primary school, they defined the Natural numbers as {1, 2, 3, ...} and Whole Numbers as {0, 1, 2, 3, ...}

[the horror]

Set Theorists want the Natural numbers defined as {0, 1, 2, 3, ...} and anyone who begins counting at 1 is an idiot.

As Relational Database Theory came directly from Set Theory, it is astounding that an RBDMS system like MySQL would make defining a zero record so difficult.

The behavior of MySQL is even more astounding since this RDBMS is usually paired with languages like C, PHP or Java that use offsets (not counters) for arrays. The first position in such arrays is 0, not 1. In Java the first element in myArray is myArray[0]. In Basic, myArray[1] is the first element.

As mentioned, I've been shouted down by Set Theory Nazis who ridicule neophytes who start sets with 1. So it is amusing to read MySQL forum posts with people equally convinced that SQL Tables should exclude a zero row.

Personally, I think it best to realize that sets beginning with 1 or 0 are valid logical constructs with different properties. IMHO, a RDBMS should facilitate both programming styles. As such NO_AUTO_VALUE_ON_ZERO should be the default.

No comments:

Post a Comment