How to Resolve MySQL Foreign Key Constraint Errors When Adding a Foreign Key?

Fixing Foreign Key Addition Error in MySQL Table

Issue:

You want to add a foreign key to an existing MySQL table but encounter the following errors:

***Error Code: 1005. Can't create table 'mytable.#sql-7fb1_7d3a' (errno: 150)***
***Error in foreign key constraint of table mytable.#sql-7fb1_7d3a:***
FOREIGN KEY (`Sprache`) REFERENCES `Sprache` (`ID`) ON DELETE SET NULL ON UPDATE SET NULL:

Resolution:

To resolve the error, ensure that the table you are referencing actually exists and has the correct column names. The error indicates that Sprache table does not exist or has no column named ID.

Revised Query:

ALTER TABLE katalog
ADD CONSTRAINT fk_katalog_sprache
FOREIGN KEY (`Sprache`)
REFERENCES Sprache(ID)
ON DELETE SET NULL
ON UPDATE SET NULL;

Additional Notes:

• Both tables (katalog and Sprache) must be InnoDB and have corresponding data types (INT(11) and NOT NULL) for the referenced columns.
• The foreign key column (Sprache) in the katalog table must not contain any NULL values before adding the constraint.
• The referenced column (ID in this case) in the Sprache table should be the primary key.

General Foreign Key Addition Syntax:

ALTER TABLE <table_name>
ADD FOREIGN KEY (<column_name>)
REFERENCES <foreign_table_name>(<foreign_column_name>);</foreign_column_name></foreign_table_name></column_name></table_name>

By following these revised instructions, you should be able to successfully add the foreign key to the katalog table in MySQL.

The above is the detailed content of How to Resolve MySQL Foreign Key Constraint Errors When Adding a Foreign Key?. For more information, please follow other related articles on the PHP Chinese website!