CODEWITHSUNDEEP
mysqlforeign-keys
kowal20
kowal20

Reputation: 19

How create a table in mysql?

I want to create a table 'post', but show me error code error 150 "Foreign key constraint is incorrectly formed". I think the structure of table is ok, but I don't know why I can't create it. 

CREATE TABLE `post_like` (
  `post_like_id` int(11) NOT NULL,
  `post_id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  INDEX idx_post_id (post_id),
  INDEX idx_user_id (user_id),
  FOREIGN KEY(`post_id`) REFERENCES  post(`post_id`) ON DELETE CASCADE ON UPDATE CASCADE,
  FOREIGN KEY(`user_id`) REFERENCES  user(`user_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_polish_ci;



CREATE TABLE `post` (
  `post_id`  int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  `comment` varchar(300) NOT NULL,
  `date_to_add` date NOT NULL,
  `time_to_add` time NOT NULL, 
  INDEX idx_user_id (user_id),
  FOREIGN KEY(`user_id`) REFERENCES  user(`user_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE= InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_polish_ci ;

CREATE TABLE `user` (
  `user_id`  int(11) NOT NULL AUTO_INCREMENT,
  `login` varchar(20)  NOT NULL,
  `password` tinytext NOT NULL,
PRIMARY KEY (user_id)
) ENGINE= InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_polish_ci;

Upvotes: 0

Views: 91

Answers (1)

Andrew Markhai
Andrew Markhai

Reputation: 194

  1. In the post table add index INDEX idx_post_id (post_id)
  2. Make sure, that your create tables in this order: user, post, post_like

I tried this in all tables created successfully, after adding index for post_id there is no "Foreign key constraint is incorrectly formed" error

If you want to see details, run SHOW ENGINE INNODB STATUS; after throwing error, in the LATEST FOREIGN KEY ERROR section you will see something like this:

Create table post_like with foreign key ( post_id ) constraint failed.There is no index in the referenced table where the referenced columns appear as the first columns

Upvotes: 1

Related Questions