MySQL ERROR 1005 (HY000)：创建外键关联表失败求助

Hey, I’ve hit this exact ERROR 1005 (HY000): Can't create table issue more times than I can count—total headache when you’re just trying to link two tables! Let’s walk through the most common fixes for your specific case with the statement:

ALTER TABLE requests ADD FOREIGN KEY FK_UserRequest(device_id) REFERENCES users(device_id)

Here’s what you should check step by step:

• Matching Data Types (Non-Negotiable)
  The device_id column in both requests and users must be identical in every way: same data type (e.g., INT vs BIGINT), same unsigned status, same length, and same nullable setting (foreign key columns almost always need to be NOT NULL).
  To verify, run these commands and compare the output for device_id:

  DESCRIBE users;
  DESCRIBE requests;
  

  If they don’t match, alter the requests column to match users first (e.g., ALTER TABLE requests MODIFY device_id INT UNSIGNED NOT NULL;).

• Referenced Column Must Be Indexed
  MySQL requires the column you’re referencing (users.device_id) to have an index—either as a primary key or a regular index. To check if it’s indexed:

  SHOW INDEX FROM users;
  

  If device_id isn’t listed, add an index first:

  ALTER TABLE users ADD INDEX idx_device_id(device_id);
  

• Both Tables Must Use InnoDB Engine
  MyISAM (MySQL’s older default engine) doesn’t support foreign keys. Confirm both tables use InnoDB:

  SHOW TABLE STATUS LIKE 'users';
  SHOW TABLE STATUS LIKE 'requests';
  

  Look for the Engine column. If either is MyISAM, convert it:

  ALTER TABLE users ENGINE=InnoDB;
  ALTER TABLE requests ENGINE=InnoDB;
  

• No Orphaned Records in requests
  If requests already has data, every device_id value in it must exist in users.device_id. To check for orphaned rows:

  SELECT device_id FROM requests WHERE device_id NOT IN (SELECT device_id FROM users);
  

  If this returns any results, delete those rows or add matching entries to users before creating the foreign key.

• Unique Foreign Key Name
  Make sure FK_UserRequest isn’t already used as a foreign key name in your database. If you’ve tried creating this foreign key multiple times, there might be a residual lock or duplicate name. Try renaming it to something unique, like FK_Requests_Users_DeviceID:

  ALTER TABLE requests 
  ADD FOREIGN KEY FK_Requests_Users_DeviceID(device_id) 
  REFERENCES users(device_id);
  

One of these fixes almost always resolves the ERROR 1005 issue for foreign keys. Start with the data type check—it’s the most common culprit!

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