Relational databases are really cool. When you get experienced with them, you can leverage all sorts of features to make sure your data is solid. A good database schema will provide a good check against buggy code trying to mess up your data. One of my favorite ways to enforce integrity is the usage of NOT NULL constraints. I recently added some to a MySQL database I’m working on, and I noticed something unusual…
mysql> alter table SERVICES add SERVICE_KEY varchar(64) NOT NULL UNIQUE;
Query OK, 1 row affected (0.16 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> describe SERVICES;
+------------------+---------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------------+---------------+------+-----+---------+----------------+
| SERVICE_ID | bigint(20) | NO | PRI | NULL | auto_increment |
| DESCRIPTION | varchar(128) | YES | | NULL | |
| COMMENTS | varchar(64) | YES | | NULL | |
| ACTIVE | varchar(3) | YES | | NULL | |
| CREATED_BY | varchar(128) | YES | | NULL | |
| CREATED_DATE | datetime | YES | | NULL | |
| UPDATED_BY | varchar(128) | YES | | NULL | |
| UPDATED_DATE | datetime | YES | | NULL | |
| TYPE | varchar(4) | YES | | NULL | |
| URL | varchar(1024) | YES | | NULL | |
| MAX_TRAN_THREADS | smallint(6) | YES | | NULL | |
| MAX_SYNC_THREADS | smallint(6) | YES | | NULL | |
| SERVICE_KEY | varchar(64) | NO | UNI | | |
+------------------+---------------+------+-----+---------+----------------+
As you may notice, the default value is somewhat nebulous on this new column. Normally the default will show NULL, which is good. In this case, it looks like it is either “nothing” which I would equate to NULL, or ”, which is horrible because it breaks the whole purpose of having the constraint. Since I trust MySQL to generally do the wrong thing, I test it:
mysql> insert into SERVICES (SERVICE_ID) values (NULL);
Query OK, 1 row affected, 1 warning (0.00 sec)
And of course, it is taking my null SERVICE_KEY value and turning it into ”. So now, if my app is broken and trying to insert null into SERVICE_KEY, MySQL will save the day and “fix” the data, completely eliminating any errors from being produced that would have revealed the bug in my code.
To quote the famous fashion designer Mugatu, I feel like I’m taking crazy pills! Everyone seems to love MySQL, but it seems completely obvious to me that it is not even a candidate to be considered when writing anything moderately serious.
Al's Blog 