Error 1215 hy000 mysql



MySQL Error Code 1215: “Cannot add foreign key constraint”

Updated 7-05-2019

In this blog, we’ll look at how to resolve MySQL error code 1215: “Cannot add foreign key constraint”.

Our Support customers often come to us with things like “My database deployment fails with error 1215”, “Am trying to create a foreign key and can’t get it working” or “Why am I unable to create a constraint?” To be honest, the error message doesn’t help much. You just get the following line:

ERROR 1215 (HY000): Cannot add foreign key constraint

There’s actually a multitude of reasons this can happen, and in this blog post is a compendium of the most common reasons why you can get MySQL Error Code 1215, how to diagnose your case to find which one is affecting you, and potential solutions for adding the foreign key.

(Note: be careful when applying the proposed solutions, as many involve ALTERing the parent table and that can take a long time blocking the table, depending on your table size, MySQL version and the specific ALTER operation being applied; In many cases using pt-online-schema-change will be likely a good idea).

So, onto the solutions:

The best way to start investigating this error is by getting more information about it from LATEST FOREIGN KEY ERROR section of SHOW ENGINE INNODB STATUS . This will give you a hint regarding the problem, which should help you identify your case in the list below.

1) The table or index the constraint refers to does not exist yet (usual when loading dumps).

How to diagnose: Run SHOW TABLES or SHOW CREATE TABLE for each of the parent tables. If you get error 1146 for any of them, it means tables are being created in the wrong order.
How to fix: Run the missing CREATE TABLE and try again, or temporarily disable foreign-key-checks. This is especially needed during backup restores where circular references might exist. Simply run:

Источник

MySQL — Error Code 1215, cannot add foreign key constraint

i got these two succesfull queries:

but when i try this one:

i get «Error Code: 1215, cannot add foreign key constraint»

i dont know what am i doing wrong.

6 Answers 6

In MySql, a foreign key reference needs to reference to an index (including primary key), where the first part of the index matches the foreign key field. If you create an an index on condition_code or change the primary key st that condition_code is first you should be able to create the index.

To define a foreign key , the referenced parent field must have an index defined on it.

As per documentation on foreign key constraints:

REFERENCES tbl_name (index_col_name. )

Define an INDEX on condition_code in parent table Donors_Medical_Condition and it should be working.

But it seems you defined your tables order and references wrongly. You should have defined foreign key in Donors_Medical_Condition table but not in Donors_Medical_Conditions table. The latter seems to be a parent.

Modify your script accordingly.

They should be written as:

Refer to:

[CONSTRAINT [symbol]] FOREIGN KEY
[index_name] (index_col_name, . )
REFERENCES tbl_name (index_col_name. )
[ON DELETE reference_option]
[ON UPDATE reference_option]

reference_option:
RESTRICT | CASCADE | SET NULL | NO ACTION

A workaround for those who need a quick how-to:
FYI: My issue was NOT caused by the inconsistency of the columns’ data types/sizes, collation or InnoDB storage engine.

Читайте также:  Socket connect error network is unreachable

How to:
Download a MySQL workbench and use it’s GUI to add foreign key. That’s it!

Источник

How to fix MySQL error 1215 Cannot add foreign key constraint

Posted on Nov 13, 2021

Learn how to fix MySQL error 1215 Cannot add foreign key constraint with this tutorial

When you try to create a foreign key constraint between two tables, you may encounter the MySQL error 1215 that says Cannot add foreign key constraint .

For example, suppose we have a table called cities with the following data:

Then, suppose we want to create a table named users with a foreign key constraint, referencing the id column from the cities table.

Here’s how we might do it:

The response from MySQL may look like this:

Unfortunately, there are many issues that could cause this error.

This tutorial will list the most common cause for ERROR 1215 and give you suggestions on how to fix them.

Make sure that you are using the correct syntax

The first thing to do is to make sure that you are using the correct syntax for creating the FOREIGN KEY constraint.

The syntax to add a foreign key on CREATE TABLE statement must follow this pattern:

You must replace [target_column_name] next to the FOREIGN KEY syntax with the column name in the current table, while [origin_table_name] and [origin_column_name] must refer to the table and column name of an existing table.

Once you have the correct syntax, make sure that there’s no typo in [target_column_name] , [origin_table_name] , and [origin_column_name] or you may trigger the same error.

Once you are sure you have the correct syntax, let’s check the engine used by your tables next.

Make sure your tables are using InnoDB engine

You need to check whether the existing table and the table you want to create are using InnoDB engine.

This is because the MyISAM engine doesn’t support adding foreign key constraints, so when you try to add a foreign key constraint to the table, it will trigger the ERROR 1215 .

To check the engine of your existing table, you need to run the SHOW TABLE STATUS statement like this:

If you’re using the mysql command line client, then add a \G next to the table name to organize the output as lists instead of a table.

Here’s an example output from the command line client:

As you can see from the highlighted line, the cities table is using the MyISAM engine.

You can change the engine of your MySQL table by using the ALTER TABLE statement as follows:

Once you altered the table engine, you can try to add the foreign key constraint to the new table again.

The default engine used for CREATE TABLE statement should be InnoDB , but you can add the engine explicitly as shown below:

If the error still happens, then it’s time to check the data type of the two columns.

Make sure the two columns are using the same data type

When adding foreign key constraints, the referenced column and the referencing column must both have the same data type.

Читайте также:  Error 525 что это такое

An important tip here is to look at the full specification of your column using the DESCRIBE statement.

As you can see from the output above, the field id has the data type of int unsigned , but the referencing column city_id on the CREATE TABLE statement has the int type:

Keep in mind that the two columns type for the foreign key constraint must exactly match ( int signed with int signed , or int unsigned with int unsigned ).

You need to fix this issue by either altering the referenced column or the referencing column until they have the same type

Now that you have the same type for the two columns, you can try adding the foreign key again.

Adding ON DELETE / UPDATE SET NULL clause on a NOT NULL column

One more thing that could cause this error is when you add the ON DELETE SET NULL clause to the FOREIGN KEY constraint while the actual column is set to NOT NULL

Take a look at the highlighted lines on the example below:

While the city_id column is specified as NOT NULL , the ON DELETE SET NULL clause on the FOREIGN KEY constraint will cause the same error.

You need to either set the column as DEFAULT NULL :

Or you need to remove the ON DELETE SET NULL clause.

The same thing also happens when you add the ON UPDATE SET NULL clause to the FOREIGN KEY constraint.

For VARCHAR columns, make sure you have the same collation for both tables

When you’re adding a foreign key constraint with columns of VARCHAR types, you need to make sure that both tables are using the same collation.

Just like the engine type, you can check the table collation using the SHOW TABLE STATUS statement.

Here’s an example output from my database:

Then, you can check the Collation and Charset you need to use in your CREATE TABLE statement by running the SHOW COLLATION statement as follows:

The result for collation utf8mb4_0900_ai_ci is as follows:

In your CREATE TABLE statement, add the COLLATE and CHARSET options as shown below:

That should allow you to add foreign key constraints with columns of VARCHAR type.

Conclusion

Through this tutorial, you’ve learned five things that you can check to resolve the MySQL error 1215 Cannot add foreign key constraint .

This error message is not helpful when trying to find the cause, and in recent MySQL versions, the error has been replaced with more descriptive ones.

For example, when you type the wrong table name, you’ll have ERROR 1824 saying Failed to open the referenced table as shown below:

The error message above directly points you to the problem with the syntax.

In another example, different column data types will make MySQL throw ERROR 3780 saying the columns are incompatible:

Unfortunately, I wasn’t able to pinpoint the exact MySQL version that updates the error messages.

I have updated MySQL to the latest version 8.0.27 , so if you have some free time, you might want to upgrade your MySQL version to at least version 8 so that it gives more helpful error messages.

Читайте также:  Error assigning value 0 to attribute gpumemorytransferrateoffset

Good luck in resolving the error! 👍

Level up your programming skills

I’m sending out an occasional email with the latest programming tutorials. Drop your email in the box below and I’ll send new stuff straight into your inbox!

About

Nathan Sebhastian is a software engineer with a passion for writing tech tutorials.
Learn JavaScript and other web development technology concepts through easy-to-understand explanations written in plain English.

Источник

Cannot add foreign key constraint — MySQL ERROR 1215 (HY000)

I am trying to create database for gym management system, but I can’t figure out why I am getting this error. I’ve tried to search for the answer here, but I couldn’t find it.

Here is the parent tables.

Can you guys help me with this problem? Thanks.

8 Answers 8

If you ever want to find out, why that error was , all you have to do is run below command and look for «LATEST FOREIGN KEY ERROR«

You will know the reason for your such errors.

For a field to be defined as a foreign key , the referenced parent field must have an index defined on it.

As per documentation on foreign key constraints:

REFERENCES parent_tbl_name (index_col_name. )

Define an INDEX on workouts.workoutName , paymentFor.paymentName , and supplements.supplementName respectively. And make sure that child column definitions must match with those of their parent column definitions.

Change workouts table definition as below:

Change supplements table definition as below:

Change paymentFor table definition as below:

Now, change child table definition as below:

Refer to:

[CONSTRAINT [symbol]] FOREIGN KEY
[index_name] (index_col_name, . )
REFERENCES tbl_name (index_col_name. )
[ON DELETE reference_option]
[ON UPDATE reference_option]

reference_option:
RESTRICT | CASCADE | SET NULL | NO ACTION

Foreign Keys are a way of implementing relationships/constraints between columns in different tables.

There are different categories of constraints that influence how they’re enforced when a row is updated or deleted from the parent table:

◾ Cascade : If a row is deleted from the parent then any rows in the child table with a matching FK value will also be deleted. Similarly for changes to the value in the parent table.

◾ Restrict : A row cannot be deleted from the parent table if this would break a FK constraint with the child table. Similarly for changes to the value in the parent table.

◾ No Action : Very similar to “Restrict” except that any events/triggers on the parent table will be executed before the constraint is enforced – giving the application writer the option to resolve any FK constraint conflicts using a stored procedure.

◾ Set NULL : If NULL is a permitted value for the FK column in the child table then it will be set to NULL if the associated data in the parent table is updated or deleted.

◾ Set Default : If there is a default value for the FK column in the child table then it will be used if the associated data in the parent table is updated or deleted. Note that this is not implemented in this version – the constraint can be added to the schema but any subsequent deletion or update to the column in the parent table will fail.

Источник

Оцените статью
toolgir.ru
Adblock
detector