Hey there, let's dig into why you're hitting that #1005 - Can't create table error when adding four foreign keys to your transactions table. Since removing one foreign key lets the table create successfully, the issue is almost certainly tied to one specific constraint's setup—here are the most common culprits to check:

• Exact Data Type & Attribute Mismatch: This is the #1 cause of errno 150. The foreign key field in transactions must match the associated primary/unique field in the parent table exactly. That means:

  • Same core data type (e.g., INT can't pair with BIGINT)
  • Same length specification (e.g., INT(11) vs INT(10) won't work)
  • Same UNSIGNED/SIGNED status (if the parent field is INT UNSIGNED, your foreign key must be too)
  • No accidental AUTO_INCREMENT on the foreign key field (that's only for primary keys)

• Parent Field Isn't a Primary or Unique Key: InnoDB requires that the field you're referencing in the parent table is either a PRIMARY KEY or has a UNIQUE constraint applied. If one of your four referenced fields is just a regular non-unique column, that constraint will fail immediately.

• Character Set/Collation Mismatch: Even if your database is set to UTF-8 globally, check the field-level collation. For example, if the parent table's key uses utf8mb4_unicode_ci but your foreign key field uses utf8_general_ci, the constraint will break. Make sure both fields share the exact same collation and character set.

• Duplicate Foreign Key Name: InnoDB enforces unique foreign key names across the entire database. If one of your four foreign key names matches an existing foreign key (even in a different table), you'll hit this error. Double-check the names you've assigned to each constraint.

• Typos or Missing Parent Resources: It sounds like your parent tables exist since removing one constraint works, but typos happen easily—confirm that all four parent tables are created, and the exact referenced fields exist in them (no misspellings in column names).

Quick Debugging Trick #

To pinpoint which constraint is faulty, try adding the foreign keys one by one in phpMyAdmin. The one that triggers the error is the problematic one—then you can compare its setup directly against the parent table's field to spot the mismatch.

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