Posts Tagged ‘ constraint ’

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.

%d bloggers like this: