One of the cause or solution for mysql errorNo 1005 : Can’t create table

This may seem minor issue for MYSQL Guru but in on one of the J2EE application I came across an unusual error (at least for me) while creating one of the child table having foreign key constraint. The error was “Can’t create table address (errno: 150)”. For eager seeker like me who don’t want to read whole story can directly jump to last two paragraph for solution/cause of the problem.

Below are the table definitions I was using for creating person and address entities :

CREATE TABLE `person` (
`id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
`name` VARCHAR(45) NOT NULL,
PRIMARY KEY (`id`)
)
ENGINE = InnoDB;

CREATE TABLE `address` (
`id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
`personid` INTEGER NOT NULL,
`street` VARCHAR(45) NOT NULL,
`city` VARCHAR(45) NOT NULL,
`country` VARCHAR(45) NOT NULL,
PRIMARY KEY (`id`),
CONSTRAINT `address_person_fk` FOREIGN KEY (`personid`) REFERENCES `person` (`id`)
ON DELETE NO ACTION
ON UPDATE NO ACTION
)
ENGINE = InnoDB;

In MYSQL documentation error description was this “Table creation failed becoz of foreign key” and “All foreign key it refers to should be of same type” about the error.

I was more confused after reading error details and unable to spot my mistake. Finally a careful look to my address table I was able to correct my mistake. The problem was data type mismatch in my address table. I have used INTEGER data type in address table in foreign key relationship but it should be UNSIGNED INTEGER to be compatible with parent person table.

So one of the solution for mysql errorNo 1005 : Can’t create table is to make foreign key relationship between two same data type only. Hope this will save someone’s time, thanks for reading.

Advertisements
  1. Then definitely I should learn MySQL from U who is one of MySQL ‘GURU’…

  1. No trackbacks 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: