Donson Donson - 2 months ago 14
MySQL Question

Error Code: 1005 Can't create table (MySQL)

For some reason I can't run this SQL script on MySQL. I believe I have all the foreign keys set in place also. It runs fine if the variable type is an

INT
, however, if it's
VARCHAR
it gives me an error code # 1005. Anybody know what's going on?

DROP TABLE IF EXISTS `test_table_2`;
DROP TABLE IF EXISTS `test_table_1`;

CREATE TABLE `test_table_1` (
ID int NOT NULL AUTO_INCREMENT,
`color` VARCHAR(15) NOT NULL,
PRIMARY KEY(ID)
) ENGINE = InnoDB DEFAULT CHARSET = utf8;

CREATE TABLE `test_table_2` (
ID int NOT NULL AUTO_INCREMENT,
`test_id` int(10) NOT NULL,
`color` VARCHAR(15) NOT NULL,
PRIMARY KEY(ID),
CONSTRAINT `fk_color` FOREIGN KEY (`color`) REFERENCES `test_table_1` (`ID`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8;


The problem resides with the line:

CONSTRAINT `fk_color` FOREIGN KEY (`color`) REFERENCES `test_table_1` (`ID`)

Answer

You are trying to refer ID column of type INT from test_table_1 to Color column in test_table_2 so it should be of type INT

CREATE TABLE `test_table_2` (
    ID int NOT NULL AUTO_INCREMENT,
    `test_id` int(10) NOT NULL,
    `color` INT NOT NULL, --here
    PRIMARY KEY(ID),
    CONSTRAINT `fk_color` FOREIGN KEY (`color`) REFERENCES `test_table_1` (`ID`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8;

Basically reference column must have the same data type as the parent table column

Comments