创建post_likes表触发MySQL #1005外键约束错误求助

Let's dive into why you're running into this errno: 150 foreign key error and how to fix it—this is one of the most common MySQL constraint issues, and the fix is usually straightforward once you spot the root cause.

First, let's look at the most likely culprit in your script: table creation order.

The Core Issue: Referenced Tables Don't Exist Yet #

When you try to create post_likes, its foreign keys reference posts.id and users.id—but in your current script, you're creating post_likes before posts and users! MySQL can't create a foreign key constraint pointing to a table that hasn't been created yet. That's almost certainly why you're seeing the error.

Additional Quick Checks (Just to Be Thorough) #

Before we fix the order, let's confirm there are no other mismatches that could trigger this error:

• Column data types match: Your post_likes.post_id and posts.id are both int(11) unsigned NOT NULL, same with post_likes.user_id and users.id—this checks out.
• Engine consistency: All tables use InnoDB (required for foreign keys)—good.
• Referenced columns are keys: posts.id and users.id are both primary keys—foreign keys can only reference primary or unique keys, so this is valid.
• Character sets match: All tables use latin1—no mismatches here.

Step-by-Step Fix #

Reorder your table creation statements so that the tables being referenced are created before the table that uses them as foreign keys. Here's the corrected order for your script:

1. users (since multiple tables reference it: login_tokens, password_tokens, posts, post_likes)
2. posts (referenced by post_likes)
3. followers (no foreign key dependencies)
4. login_tokens (depends on users)
5. password_tokens (depends on users)
6. post_likes (depends on users and posts)

Here's the adjusted snippet for the table creation section:

# Dump of table users 
# ------------------------------------------------------------ 
DROP TABLE IF EXISTS `users`; 
CREATE TABLE `users` ( 
 `id` int(11) unsigned NOT NULL AUTO_INCREMENT, 
 `username` varchar(32) DEFAULT NULL, 
 `password` varchar(60) DEFAULT NULL, 
 `email` text, 
 `verified` tinyint(1) NOT NULL DEFAULT '0', 
 PRIMARY KEY (`id`) 
) ENGINE=InnoDB DEFAULT CHARSET=latin1; 

# Dump of table posts 
# ------------------------------------------------------------ 
DROP TABLE IF EXISTS `posts`; 
CREATE TABLE `posts` ( 
 `id` int(11) unsigned NOT NULL AUTO_INCREMENT, 
 `body` varchar(160) NOT NULL DEFAULT '', 
 `posted_at` datetime NOT NULL, 
 `user_id` int(11) unsigned NOT NULL, 
 `likes` int(11) unsigned NOT NULL, 
 PRIMARY KEY (`id`), 
 KEY `user_id` (`user_id`), 
 CONSTRAINT `posts_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) 
) ENGINE=InnoDB DEFAULT CHARSET=latin1; 

# Dump of table followers 
# ------------------------------------------------------------ 
DROP TABLE IF EXISTS `followers`; 
CREATE TABLE `followers` ( 
 `id` int(11) unsigned NOT NULL AUTO_INCREMENT, 
 `user_id` int(11) unsigned NOT NULL, 
 `follower_id` int(11) unsigned NOT NULL, 
 PRIMARY KEY (`id`) 
) ENGINE=InnoDB DEFAULT CHARSET=latin1; 

# Dump of table login_tokens 
# ------------------------------------------------------------ 
DROP TABLE IF EXISTS `login_tokens`; 
CREATE TABLE `login_tokens` ( 
 `id` int(11) unsigned NOT NULL AUTO_INCREMENT, 
 `token` char(64) NOT NULL DEFAULT '', 
 `user_id` int(11) unsigned NOT NULL, 
 PRIMARY KEY (`id`), 
 UNIQUE KEY `token` (`token`), 
 KEY `user_id` (`user_id`), 
 CONSTRAINT `login_tokens_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) 
) ENGINE=InnoDB DEFAULT CHARSET=latin1; 

# Dump of table password_tokens 
# ------------------------------------------------------------ 
DROP TABLE IF EXISTS `password_tokens`; 
CREATE TABLE `password_tokens` ( 
 `id` int(11) unsigned NOT NULL AUTO_INCREMENT, 
 `token` char(64) NOT NULL DEFAULT '', 
 `user_id` int(11) unsigned NOT NULL, 
 PRIMARY KEY (`id`), 
 UNIQUE KEY `token` (`token`) 
) ENGINE=InnoDB DEFAULT CHARSET=latin1; 

# Dump of table post_likes 
# ------------------------------------------------------------ 
DROP TABLE IF EXISTS `post_likes`; 
CREATE TABLE `post_likes` ( 
 `id` int(11) unsigned NOT NULL AUTO_INCREMENT, 
 `post_id` int(11) unsigned NOT NULL, 
 `user_id` int(11) unsigned NOT NULL, 
 PRIMARY KEY (`id`), 
 KEY `post_id` (`post_id`), 
 KEY `user_id` (`user_id`), 
 CONSTRAINT `post_likes_ibfk_1` FOREIGN KEY (`post_id`) REFERENCES `posts` (`id`), 
 CONSTRAINT `post_likes_ibfk_2` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) 
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Final Notes #

Run the adjusted script, and the post_likes table should create without issues. If you ever hit error 150 again, always first check:

• Are the referenced tables created before the table with the foreign key?
• Do the column data types (including unsigned/signed status) match exactly between the foreign key and referenced column?
• Is the referenced column a primary key or unique key?

内容的提问来源于stack exchange，提问作者Shagbaor Agber