CODEWITHSUNDEEP
mysqlsql
A.M
A.M

Reputation: 63

mysql table creation Error Code: 1005 in this specific case

I have tried everything in solving this issue and yes I know that this type of question is already asked here but I could not solve my issue It is mysql database

 Error Code: 1005

Can't create table '.\project\comments.frm' (errno: 150)

the foreign keys are matching in structure (i.e length and type) then what can be the possible problem in the table creation

Table which is giving error is comments:

 CREATE TABLE `comments`(
   `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
   `description` VARCHAR(100) NOT NULL,
   `user_id` INT(10) UNSIGNED NOT NULL,
   `post_id` INT(10) UNSIGNED NOT NULL,
   FOREIGN KEY (`post_id`) REFERENCES `posts`.`id`,
   FOREIGN KEY (`user_id`) REFERENCES `users`.`id`,
   PRIMARY KEY  (`id`)
 ) ENGINE=INNODB DEFAULT CHARSET=latin1;`

Here is posts table which is already created in the databas

 CREATE TABLE `posts` (
   `id` int(10) unsigned NOT NULL auto_increment,
   `title` varchar(30) default NULL,
   `description` longtext,
   `image` varchar(50) default NULL,
   PRIMARY KEY  (`id`)
 ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Here is the users table which is also already created in the database

 CREATE TABLE `users` (
    `id` int(10) unsigned NOT NULL auto_increment,
    `user_name` varchar(33) default NULL,
    `email` varchar(255) NOT NULL,
    `password` varchar(255) default NULL,
    `type` varchar(255) NOT NULL,
    `registrationDate` date NOT NULL,
    PRIMARY KEY  (`id`,`email`,`type`)
)

Upvotes: 0

Views: 44

Answers (1)

eggyal
eggyal

Reputation: 125835

Your syntax is incorrect. The REFERENCES keyword should be followed by table (columns):

CREATE TABLE `comments`(
    `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    `description` VARCHAR(100) NOT NULL,
    `user_id` INT(10) UNSIGNED NOT NULL,
    `post_id` INT(10) UNSIGNED NOT NULL,
    FOREIGN KEY (`post_id`) REFERENCES `posts` (`id`),
    FOREIGN KEY (`user_id`) REFERENCES `users` (`id`),
    PRIMARY KEY  (`id`)
) ENGINE=INNODB DEFAULT CHARSET=latin1;

Upvotes: 1

Related Questions