Ad

#1005 - Can't Create Table 'addfriend.updates' (errno: 150)

- 1 answer

I want to create this table but it just split off with this error:

1005 - Can't create table 'jotech.updates' (errno: 150)

A friend of mine says is because foreign key constraint was not correctly formed. But he doesn't know how to fix it.

please help.

    CREATE TABLE `users` (
`user_id` INT(11) NOT NULL AUTO_INCREMENT ,
`username` VARCHAR(45) ,
`password` VARCHAR(100) ,
`email` VARCHAR(45) ,
`friend_count` INT(11) ,
`profile_pic` VARCHAR(150),
PRIMARY KEY (`user_id`));



    CREATE TABLE `updates` (
`update_id` INT(11) AUTO_INCREMENT ,
`update` VARCHAR(45),
`user_id_fk` VARCHAR(45),
`created` INT(11) ,
`ip` VARCHAR(45),
PRIMARY KEY (`update_id`),
FOREIGN KEY (user_id_fk) REFERENCES users(user_id));


CREATE TABLE `friends` (
`friend_one` INT(11) ,
`friend_two` INT(11) ,
`status` ENUM('0','1','2') DEFAULT '0',
PRIMARY KEY (`friend_one`,`friend_two`),
FOREIGN KEY (friend_one) REFERENCES users(user_id),
FOREIGN KEY (friend_two) REFERENCES users(user_id));
Ad

Answer

The datatypes of the foreign key column(s) has to match exactly the datatype of the column(s) it references.

The problem is here:

 `user_id_fk` VARCHAR(45)
              ^^^^^^^^^^^

That can't be a foreign key column reference to:

 `user_id` INT(11)
           ^^^^^^^

The foreign key column in updates would need to be defined:

 `user_id_fk` INT(11)
              ^^^^^^^

In order to define the foreign key constraint.

Ad
source: stackoverflow.com
Ad