BenV's notes

Another reason to avoid MySQL

Today MySQL has managed to piss me off enough to get rid of it for something I was working on.
The case:
I just created a brand new database. In that database I want to store items, and those items should be linked to users.
So as a first stab I had this CREATE TABLE statement:

CREATE TABLE user (user_id SERIAL, username varchar(255) UNIQUE NOT NULL, password char(32) NOT NULL, PRIMARY KEY (user_id));
CREATE TABLE item (item_id SERIAL, name varchar(255) NOT NULL UNIQUE, user_id bigint unsigned REFERENCES user(user_id),PRIMARY KEY (item_id)) ENGINE = InnoDB;

Guess what MySQL said? Of course, it said “Sure thing dude!”. And to make sure you believe me, here’s the verbatim:

mysql> CREATE TABLE user (user_id SERIAL, username varchar(255) UNIQUE NOT NULL, password char(32) NOT NULL, PRIMARY KEY (user_id));
Query OK, 0 rows affected (0.36 sec)
mysql> show warnings;
Empty set (0.00 sec)
mysql> CREATE TABLE item (item_id SERIAL, name varchar(255) NOT NULL UNIQUE, user_id bigint unsigned REFERENCES user(user_id),PRIMARY KEY (item_id)) ENGINE = InnoDB;
Query OK, 0 rows affected (0.32 sec)
mysql> show warnings;
Empty set (0.00 sec)

Well guess what….
IT LIED!
Those references to the user table? What references? (continue reading…)