Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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)
share|improve this question
add comment

3 Answers

up vote 4 down vote accepted

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.

share|improve this answer
    
perfect dude it resolves my problem beacuse as you have mentioned the datatypes are different of both coloumns thanks for pointing me –  M Khalid Junaid Sep 20 '12 at 8:33
1  
No problem - glad I could help. –  Mr.Brownstone Sep 20 '12 at 8:39
add comment

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]
share|improve this answer
add comment

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
share|improve this answer
1  
What does this answer provide that hasn't been already provided by the two previous answers? –  ypercube Sep 21 '12 at 7:00
    
i have alredy accepted the answer and your provided answer is worng so please remove that answer before downvoting –  M Khalid Junaid Sep 21 '12 at 10:35
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.