Mysql error while creating FK

Question

here is structure of both tables

category table
CREATE TABLE category (
`catID` bigint(20) NOT NULL AUTO_INCREMENT,
`title` varchar(60) NOT NULL,
`created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
 PRIMARY KEY (`catID`)
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=latin1;

and the second table projects

CREATE TABLE `projects` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`catID` int(11) NOT NULL,
`industryID` int(11) NOT NULL,
 `dsID` int(11) NOT NULL,
`countryID` int(11) NOT NULL,
`title` varchar(255) NOT NULL,
PRIMARY KEY (`id`),
KEY `catID` (`catID`),
KEY `industryID` (`industryID`),
KEY `dsID` (`dsID`),
KEY `countryID` (`countryID`)
 ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Now to add FK to tables here is the query but i keep getting the error

Can't create table .... (errno: 150)
ALTER TABLE `projects`  ADD CONSTRAINT FK_catID FOREIGN KEY projects (catID)  REFERENCES category (catID)

Answer 1

When you create a foreign key between two tables the data type of the columns that are taking part in the relationship must have the same data type. In the example you have posted above it is failing because the Categories table defines CatID as a BIGINT wheras in the Projects table you have defined it as a INT. Changing the data type of CatID in the Projects table to BIGINT should resolve your problem.

I hope this helps you.

Answer 2

The data type/size/signedness have to be identical on a foreign key constraint.

`category`.`catID` bigint(20)
`projects`.`catID` int(11)

Use bigint for both or int for both.

Also, this statement is probably not going to do exactly what you intended, even when it works:

ALTER TABLE `projects` ADD CONSTRAINT FK_catID FOREIGN KEY projects (catID) REFERENCES category (catID)
-- this position is an optional index name, not a table name ^^^^^

You probably intend to do this, instead:

ALTER TABLE `projects` ADD CONSTRAINT FK_catID FOREIGN KEY (catID) REFERENCES category (catID)

http://dev.mysql.com/doc/refman/5.1/en/alter-table.html

ALTER TABLE tbl_name
ADD [CONSTRAINT [symbol]]
FOREIGN KEY [index_name] (index_col_name,...)
REFERENCES tbl_name (index_col_name,...)
[ON DELETE RESTRICT | CASCADE | SET NULL | NO ACTION]
[ON UPDATE RESTRICT | CASCADE | SET NULL | NO ACTION]

Answer 3

Please find the solution here.

mysql> CREATE TABLE category (
    -> `catID` bigint(20) NOT NULL AUTO_INCREMENT,
    -> `title` varchar(60) NOT NULL,
    -> `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    ->  PRIMARY KEY (`catID`)
    -> ) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.03 sec)

mysql> CREATE TABLE `projects` (
    -> `id` int(11) NOT NULL AUTO_INCREMENT,
    -> `catID` bigint(20) NOT NULL,
    -> `industryID` int(11) NOT NULL,
    ->  `dsID` int(11) NOT NULL,
    -> `countryID` int(11) NOT NULL,
    -> `title` varchar(255) NOT NULL,
    -> PRIMARY KEY (`id`),
    -> KEY `catID` (`catID`),
    -> KEY `industryID` (`industryID`),
    -> KEY `dsID` (`dsID`),
    -> KEY `countryID` (`countryID`)
    ->  ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.00 sec)

mysql> ALTER TABLE `projects`
        ADD CONSTRAINT FK_catID FOREIGN KEY projects (catID)
            REFERENCES category (catID) ON DELETE RESTRICT;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0