Comments on: MySQL Error Code 1215: “Cannot add foreign key constraint” https://www.percona.com/blog/mysql-error-code-1215-cannot-add-foreign-key-constraint/ Thu, 05 Oct 2023 13:04:53 +0000 hourly 1 https://wordpress.org/?v=6.5.2 By: Pick Avana https://www.percona.com/blog/mysql-error-code-1215-cannot-add-foreign-key-constraint/#comment-10972608 Sat, 08 Aug 2020 03:57:59 +0000 https://www.percona.com/blog/?p=42475#comment-10972608 I solved the problem. I found that the primary keys were created using attribute “UNSIGNED” while the foreign key fields did not have that attribute. I change the foreign key fields to “UNSIGNED” and bingo! Thanks again for your article though. It helped tremendously in diving into the details and there it was, as simple as that. Thank you.

]]> By: Pick Avana https://www.percona.com/blog/mysql-error-code-1215-cannot-add-foreign-key-constraint/#comment-10972568 Fri, 31 Jul 2020 03:24:12 +0000 https://www.percona.com/blog/?p=42475#comment-10972568 The tables I was able to add a FK for had type INT(11) for Primary and foreign keys. The tables I am unsuccessfully trying to add FK to have BIGINT(20) – could that be the problem?

]]> By: Pick Avana https://www.percona.com/blog/mysql-error-code-1215-cannot-add-foreign-key-constraint/#comment-10972567 Fri, 31 Jul 2020 02:56:58 +0000 https://www.percona.com/blog/?p=42475#comment-10972567 Great read. Tried all of the above and still unable to create FK Constraint on existing tables. I created two new tables and FK without any problem but Im trying to create FK with two existing tables – both empty. I made sure no typos. I have foreign field indexed. I have same type, width for foreign key and related primary key. Foreign field is not set to NOT NULL. Tried all. Still fails. Could I provide sql dumps of two tables (no records) and see if you spot something?

]]> By: Marcos Albe https://www.percona.com/blog/mysql-error-code-1215-cannot-add-foreign-key-constraint/#comment-10972278 Thu, 28 May 2020 14:51:44 +0000 https://www.percona.com/blog/?p=42475#comment-10972278 @Deepsha: you need to wrap the name with backticks;

backtick code

So in general, whenver you want to use a reserved word it MUST be wrapped in backticks (`)

]]> By: Deepsha Lal https://www.percona.com/blog/mysql-error-code-1215-cannot-add-foreign-key-constraint/#comment-10972251 Wed, 20 May 2020 01:29:54 +0000 https://www.percona.com/blog/?p=42475#comment-10972251 So I have this assignment which mandates us to follow the given attribute and table names. Unfortunately, one of the table names is a reserved keyword- ‘Session’. Phpmyadmin doesn’t give any errors while creating the Session table but when I try to create another table that contains a foreign key referencing the Session table, I get the error “cannot add foreign key constraint”. Can you tell me how I should write the name of the session table so that phpmyadmin recognizes it as a table and not a keyword.

]]> By: Sławomir B. https://www.percona.com/blog/mysql-error-code-1215-cannot-add-foreign-key-constraint/#comment-10971940 Tue, 17 Mar 2020 01:33:39 +0000 https://www.percona.com/blog/?p=42475#comment-10971940 #5 got my error fixed – thank you for the detailed post.

]]> By: Aray https://www.percona.com/blog/mysql-error-code-1215-cannot-add-foreign-key-constraint/#comment-10970858 Tue, 04 Jun 2019 00:56:51 +0000 https://www.percona.com/blog/?p=42475#comment-10970858 Thank you for this very helpful post.

]]> By: Archie Hughes https://www.percona.com/blog/mysql-error-code-1215-cannot-add-foreign-key-constraint/#comment-10970549 Thu, 04 Apr 2019 03:32:16 +0000 https://www.percona.com/blog/?p=42475#comment-10970549 Thank you for sharing valuable information. Nice post. I enjoyed reading this post. soundcloud downloader

]]> By: Merianos Nikos https://www.percona.com/blog/mysql-error-code-1215-cannot-add-foreign-key-constraint/#comment-10970494 Sat, 23 Mar 2019 17:40:46 +0000 https://www.percona.com/blog/?p=42475#comment-10970494 Awesome post !! You saved my day 🙂 Thank you very much 🙂

]]> By: Aleksandr Mikheev (@ayamikh) https://www.percona.com/blog/mysql-error-code-1215-cannot-add-foreign-key-constraint/#comment-10970479 Tue, 19 Mar 2019 13:29:35 +0000 https://www.percona.com/blog/?p=42475#comment-10970479 Thank you! You helped me a lot!

]]> By: Diego Carvalho https://www.percona.com/blog/mysql-error-code-1215-cannot-add-foreign-key-constraint/#comment-10970438 Fri, 08 Mar 2019 12:58:53 +0000 https://www.percona.com/blog/?p=42475#comment-10970438 save me with 12°

]]> By: Fedrick https://www.percona.com/blog/mysql-error-code-1215-cannot-add-foreign-key-constraint/#comment-10970178 Sat, 12 Jan 2019 03:11:29 +0000 https://www.percona.com/blog/?p=42475#comment-10970178 Very helpful, thanks a lot for the post!

]]> By: Barclay Dunn https://www.percona.com/blog/mysql-error-code-1215-cannot-add-foreign-key-constraint/#comment-10969725 Tue, 25 Sep 2018 20:43:52 +0000 https://www.percona.com/blog/?p=42475#comment-10969725 AWESOME post! I’ve never run across different charsets being the issue before, so you saved my life too!

]]> By: Nabil https://www.percona.com/blog/mysql-error-code-1215-cannot-add-foreign-key-constraint/#comment-10968740 Fri, 01 Dec 2017 15:34:31 +0000 https://www.percona.com/blog/?p=42475#comment-10968740 Saved my life bro..Thanks

]]> By: Junaid https://www.percona.com/blog/mysql-error-code-1215-cannot-add-foreign-key-constraint/#comment-10968688 Mon, 20 Nov 2017 17:44:09 +0000 https://www.percona.com/blog/?p=42475#comment-10968688 Thanks, thats a great info,

Valeriy – your InnoDB status query helped me to understand why I was getting an error.

Basically, I was trying to ‘SET NULL’ on delete and at the same time I was defining the column as NOT NULL.

]]> By: Michael https://www.percona.com/blog/mysql-error-code-1215-cannot-add-foreign-key-constraint/#comment-10968558 Mon, 16 Oct 2017 18:15:23 +0000 https://www.percona.com/blog/?p=42475#comment-10968558 Found another reason, maybe a stupid one, but to me it happened:
If you want the constraint action to be “SET NULL”, then the child column needs to allow NULL-values in the table structure.

]]> By: Vicky Anand https://www.percona.com/blog/mysql-error-code-1215-cannot-add-foreign-key-constraint/#comment-10968465 Tue, 19 Sep 2017 22:33:58 +0000 https://www.percona.com/blog/?p=42475#comment-10968465 Thanks Amazing details, for me it was character set issue

]]> By: luke https://www.percona.com/blog/mysql-error-code-1215-cannot-add-foreign-key-constraint/#comment-10968166 Wed, 14 Jun 2017 11:55:11 +0000 https://www.percona.com/blog/?p=42475#comment-10968166 it works.Thank u very much

]]> By: KarLKoX https://www.percona.com/blog/mysql-error-code-1215-cannot-add-foreign-key-constraint/#comment-10968072 Fri, 12 May 2017 09:12:26 +0000 https://www.percona.com/blog/?p=42475#comment-10968072 Hi,
I just found another way when mySQL fails to create a FK : the columns of the referenced table MUST MATCH the order of the PK !
I didn’t found anything in the web with this restriction.

]]> By: Marcos Albe https://www.percona.com/blog/mysql-error-code-1215-cannot-add-foreign-key-constraint/#comment-10967972 Thu, 06 Apr 2017 18:41:22 +0000 https://www.percona.com/blog/?p=42475#comment-10967972 Nice! I gotta admit I never saw the errors there.

]]>