MySQL Error 1215: "Cannot add foreign key constraint"

When attempting to create a foreign key constraint in MySQL, it is crucial to ensure that both the referenced field and the foreign key field adhere to specific requirements. Here's how to diagnose and resolve this error:

Engine Consistency

• The "InnoDB" engine must be used for both tables involved.

Data Type and Length

• The data types and lengths of both fields should match exactly. For example, if the referenced field is VARCHAR(20), the foreign key field should also be VARCHAR(20).

Collation

• Collation indicates character set and sorting rules. Both fields should use the same collation, such as utf8.

Uniqueness

• The referenced field must be unique, often signified by primary or unique keys. The foreign key field cannot reference fields that allow for duplicate values.

Null Handling

• Ensure that you have not defined a SET NULL condition when some referenced columns are declared NOT NULL.

Additional Symptoms

If the error persists, run the command SHOW ENGINE INNODB STATUS; to reveal more specific details.

Incorrect Statement

The provided SQL statement creates a table named "course" with a foreign key constraint referencing the "department" table on the "dept_name" field. However, this statement is incorrect because it lacks the datatype specification for the "dept_name" field. To rectify this, the statement should be modified as follows:

create table course (
    course_id varchar(7),
    title varchar(50),
    dept_name varchar(20),
    credits numeric(2,0),
    primary key(course_id),
    foreign key (dept_name) references department(dept_name)
);