Why Am I Getting the \'Cannot Add Foreign Key Constraint\' Error in MySQL?

Foreign Key Constraint Error: Understanding Causes and Solutions

The question concerns an error encountered while creating the course table in MySQL:

ERROR 1215 (HY000): Cannot add foreign key constraint

This error indicates an issue with the foreign key constraint specified in the SQL statement. A foreign key constraint ensures that data in the foreign key column of one table (e.g., course) references existing data in the primary key column of another table (e.g., department).

To resolve this error and successfully create the course table, it is essential to follow these guidelines:

• Matching Engine, Datatype, and Collation:

  • Both the referenced field (dept_name) in the department table and the foreign key field (dept_name) in the course table must have the same engine, such as InnoDB.
  • The fields must have identical datatypes, including length. For example, both VARCHAR(20) or INT(10) UNSIGNED.
  • Collation, which specifies character set encoding, should also match. Typically, utf8 is used.

• Uniqueness of Referenced Field:

  • The field referenced by the foreign key (dept_name in the department table) must be unique. This ensures that each row in the course table can correctly reference a row in the department table.

• NOT NULL Constraints:

  • If you have specified a SET NULL option in the foreign key constraint, ensure that none of the columns involved are defined as NOT NULL.

By adhering to these guidelines, you can avoid the "Cannot add foreign key constraint" error and establish a proper referential integrity between the course and department tables.

Additional Note:

Disabling foreign key checks using SET FOREIGN_KEY_CHECKS=0 may allow you to create the table with the erroneous constraint, but it is not recommended. This setting can lead to data inconsistencies and database corruption.

The above is the detailed content of Why Am I Getting the \'Cannot Add Foreign Key Constraint\' Error in MySQL?. For more information, please follow other related articles on the PHP Chinese website!