How to Avoid MySQL Errno 150 Errors When Using Foreign Key Constraints?

MySQL Foreign Key Constraints: Avoiding Errno 150 Errors

While creating a table in MySQL with foreign keys referencing other tables, you may encounter an errno: 150 error. To resolve this issue, it's crucial to adhere to the following guidelines:

1. Ensure Table Order: Define the parent table containing the primary key before creating the child table with the foreign key reference.
2. Enable Foreign Key Support: Both the parent and child tables should support foreign key constraints, typically by using the InnoDB storage engine.
3. Left-Most Column Key: The referenced columns in the parent table must be the left-most columns of a key, ideally a PRIMARY KEY or UNIQUE KEY.
4. Matching Primary Key Order: The FK definition must align with the PK definition in terms of column order and data types.
5. Proper Data Alignment: The PK columns in the parent table and the FK columns in the child table must match in terms of data type and collation.
6. Existing Data Compatibility: If the child table contains data, ensure that each FK column value matches a corresponding value in the parent table PK columns.
7. Avoid Temporary or Partitioned Tables: Foreign key constraints cannot be defined on TEMPORARY or PARTITIONED tables.
8. Nullable Columns: If an ON DELETE SET NULL option is used, the FK columns must be nullable.
9. Unique Constraint Names: Foreign key constraints should have unique names across the schema to avoid conflicts.
10. Dependent Foreign Keys: If a malformed FK exists in another table referencing the same field in the parent table, ensure consistency before creating the new FK. Use the following query to identify problematic FK's:

SELECT * FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE CONSTRAINT_SCHEMA = 'your_schema_name'
AND CONSTRAINT_NAME <> 'PRIMARY' AND REFERENCED_COLUMN_NAME IS NOT NULL;

The above is the detailed content of How to Avoid MySQL Errno 150 Errors When Using Foreign Key Constraints?. For more information, please follow other related articles on the PHP Chinese website!