Skip to content

Pesky Common Sense Database Rules (MySQL)

May 13, 2012

Recently, I’ve found myself iterating over some fairly simple MySQL database designs of a yet still early development application.  However, several times I found myself running in to this same error:

ERROR 1005 (HY000): Can’t create table ‘test.board’ (errno: 150)

The following bit of code is an example of a subtle case of logic sneaking up behind my code and catching me off guard. See if you can spot the problem with the SQL alone.

CREATE TABLE IF NOT EXISTS test.board (
id INT(11) NOT NULL AUTO_INCREMENT,
category_id INT(11) NOT NULL,
creator INT(11) DEFAULT NULL,
created TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
name VARCHAR(64) NOT NULL,
PRIMARY KEY (id),
CONSTRAINT fk_board_cat
FOREIGN KEY (category_id) REFERENCES category(id)
ON DELETE SET NULL
ON UPDATE NO ACTION,
CONSTRAINT fk_board_creator
FOREIGN KEY (creator) REFERENCES user(id)
ON DELETE SET NULL
ON UPDATE NO ACTION
)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_unicode_ci
;

During this overall process I’ve seen the same error crop up in more than one way (note the above SQL does have a problem with it’s logic but not it’s syntax and won’t work in any case).  The error at hand which have somewhat eluded me tonight is MySQL error 1005.

ERROR 1005 (HY000): Can’t create table ‘test.board’ (errno: 150)

Can’t create table? Rather helpful advise there. 

Let’s look in to the first scenario here.  It turns out that in this case, MySQL does have more information available for us, but it is hidden away. You can bring up this extra information about the failure so long as no more foreign key errors occur before you run this next query.

SHOW ENGINE INNODB STATUS\G

This will pull up tons of information about the operation of MySQL’s InnoDB database engine (by the way, \G is a handy modifier for the mysql command line interface which formats output to fit more appropriately in a CLI environment, it takes the place of the semicolon).  The last instances of several categories of errors with deep details (sometimes all the way down to comparing tuples) will be listed in this output as well assorted other useful low-level information.  The key hint to my above problem can be seen under LATEST FOREIGN KEY ERROR as foreshadowed above.

————————
LATEST FOREIGN KEY ERROR
————————
120513 5:01:16 Error in foreign key constraint of table test/board:

FOREIGN KEY (category_id) REFERENCES category(id)
ON DELETE SET NULL
ON UPDATE NO ACTION,
CONSTRAINT fk_board_creator
FOREIGN KEY (creator) REFERENCES user(id)
ON DELETE SET NULL
ON UPDATE NO ACTION
)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_unicode_ci:
You have defined a SET NULL condition though some of the
columns are defined as NOT NULL.

This simple logic error just goes to show that developing column restrictions and foreign key restrictions in different thoughts is not a good practice.

The next occurrence of this error message might just pop up after fixing the first one.  Say we fixed the error above by removing the NOT NULL constraint from the category_id column. Expecting our code to now work, we go to run the CREATE and yet again we are struck we ERROR 1005.  And again, we can turn to SHOW ENGINE INNODB STATUS to help us out with more information and here’s our hint this time:

Cannot find an index in the referenced table where the
referenced columns appear as the first columns, or column types
in the table and the referenced table do not match for constraint.
Note that the internal storage type of ENUM and SET changed in
tables created with >= InnoDB-4.1.12, and such columns in old tables
cannot be referenced by such columns in new tables.
See http://dev.mysql.com/doc/refman/5.5/en/innodb-foreign-key-constraints.html
for correct foreign key definition.

This hint could still have several different causes, but I’ll touch only on the ones I believe are more common.

  • Column data types don’t match.
  • Data type definition includes the character set and collation if applicable when using varchar or other character based data types.  (MySQL: Foreign Key Constraints)
  • The column name or table name in the constraint definition of the child table does not correspond to the proper name for the parent table and column.

These are all common sense errors once you’ve seen them enough to know where to look and what the error messages actually mean.  However, hopefully this short guide will help someone who, like me, had trouble figuring out precisely why the tables couldn’t be created.

Advertisements
No comments yet

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: