Migration Cannot add foreign key constraint

Question

I m trying to create foreign keys in Laravel however when I migrate my table using artisan i am thrown the following error    Illuminate Database QueryException  SQLSTATE HY000   General error  1215 Cannot add foreign key constraint  SQL   alter table  priorities  add constraint priorities user id foreign foreign  key   user id   references  users    id           My migration code is as so       priorities migration file  public function up                Schema  create  priorities   function  table             table- gt increments  id   true            table- gt integer  user id             table- gt foreign  user id  - gt references  id  - gt on  users             table- gt string  priority name             table- gt smallInteger  rank             table- gt text  class             table- gt timestamps  timecreated                      Reverse the migrations         return void     public function down                Schema  drop  priorities        users migration file   public function up                Schema  table  users   function  table             table- gt create         table- gt increments  id         table- gt string  email         table- gt string  first name         table- gt string  password         table- gt string  email code         table- gt string  time created         table- gt string  ip         table- gt string  confirmed         table- gt string  user role         table- gt string  salt         table- gt string  last login          table- gt timestamps                      Reverse the migrations         return void     public function down                    Schemea  drop  users        Any ideas as to what I ve done wrong  I want to get this right now  as I ve got a lot of tables I need to create e g  Users  Clients  Projects  Tasks  Statuses  Priorities  Types  Teams  Ideally I want to create tables which hold this data with the foreign keys  i  e clients project and project tasks etc    Hope someone can help me to get started

User · Answer

If none of the solutions above work for newbies check if both IDs have the same type: both are integer or both are bigInteger, ... You can have something like this:

Main Table (users for example)

$table->bigIncrements('id');

Child Table (priorities for example)

$table->unsignedInteger('user_id');
$table->foreign('user_id')->references('id')->on('users')->onDelete('cascade');

This query will failed because users.id is a BIG INTEGER whereas priorities.user_id is an INTEGER.

The right query in this case would be the following:

$table->unsignedBigInteger('user_id');
$table->foreign('user_id')->references('id')->on('users')->onDelete('cascade');

User · Answer

make sure your foreing column is over wide rage of foreing key column  I means your foreingkey  in second table  must be same type of your ponter pricipal key  in first table   your pointer principal key must be add unsigned method  let me show   on your FIRST migration table    table- gt increments  column name      is INTEGER and UNSIGNED   on your SECOND migration table    table- gt integer  column forein name  - gt unsigned      this must be INTEGER and UNSIGNED  table- gt foreign  column forein name  - gt references  column name  - gt on  first table name        ANOTHER EXAMPLE TO SEE DIFFERENCE  on your FIRST migration table    table- gt mediumIncrements  column name      is MEDIUM-INTEGER and UNSIGNED   on your SECOND migration table    table- gt mediumInteger  column forein name  - gt unsigned      this must be MEDIUM-INTEGER and UNSIGNED  table- gt foreign  column forein name  - gt references  column name  - gt on  first table name      SEE MYSQL NUMERIC TYPES TABLE RANGES

User · Answer

In my case the problem was that the auto-generated migration for the users table was setting       table- gt bigIncrements  id          So I had to change the column type    table- gt bigInteger  id      to make my migration with the foreign key work   This with laravel 5 8 2

User · Answer

It also may be your the ordering of creation migration  If you firstly create priorities table  and after users table then it will be wrong  Because of first migration looking for users table  So  you have to change the ordering of migration on   app database migrations   directory

User · Answer

I had the same error with Laravel 5 when making a pivot table  and the problem in my case was that I didn t have  - gt onDelete  cascade

User · Answer

In my case the problem was with migration timing be careful while creating migrations firstly create the child migration than the base migration  Because if you create base migration first which have your foreign key will look for child table and there wont be table which then throw an exception  Further more  When you create migration it has a timestamp in the beginning of it  lets say you have created a migration cat so it will look like 2015 08 19 075954 the cats time php and it has this code  lt  php  use Illuminate Database Schema Blueprint  use Illuminate Database Migrations Migration   class TheCatsTime extends Migration                  Run the migrations                 return void             public function up                 Schema  create  cat   function  Blueprint  table                 table- gt increments  id                 table- gt string  name                   table- gt date  date of birth                 table- gt integer  breed id  - gt unsigned  - gt nullable                          Schema  table  cat   function  table             table- gt foreign  breed id  - gt references  id  - gt on  breed                                    Reverse the migrations                 return void             public function down                 Schema  drop  cat             And after creating the base table you create another migration breed which is child table it has its own creation time and date stamp  The code will look like    lt  php  use Illuminate Database Schema Blueprint  use Illuminate Database Migrations Migration   class BreedTime extends Migration                  Run the migrations                 return void             public function up                 Schema  create  breed   function  Blueprint  table                  table- gt increments  id                      table- gt string  name                                      Reverse the migrations                 return void             public function down                 Schema  drop  breed             it seems these both table are correct but when you run php artisan migrate  It will throw an exception because migration will first create the base table in your database because you have created this migration first and our base table has foreign key constraint in it which will look for child table and the child table doesn t exist which is probably an exception   So   Create child table migration first  Create base table migration after child migration is created  php artisan migrate   done it will work

User · Answer

One thing that I think is missing from the answers on here  and please correct me if I am wrong  but the foreign keys need to be indexed on the pivot table  At least in mysql that seems to be the case   public function up         Schema  create  image post   function  Blueprint  table             table- gt engine    InnoDB            table- gt increments  id             table- gt integer  image id  - gt unsigned  - gt index             table- gt integer  post id  - gt unsigned  - gt index             table- gt timestamps                 Schema  table  image post   function  table             table- gt foreign  image id  - gt references  id  - gt on  image  - gt onDelete  cascade             table- gt foreign  post id  - gt references  id  - gt on  post  - gt onDelete  cascade

User · Answer

We cannot add relations  unless related tables gets created  Laravel run migrations order by date of migration files  So if you want to create a relation with a table that exists in 2nd migration file it fails   I faced the same problem  so I created one more migration file at last to specify all relations   Schema  table  properties   function Blueprint  table             table- gt foreign  user  - gt references  id  - gt on  users  - gt onDelete  cascade             table- gt foreign  area  - gt references  id  - gt on  areas  - gt onDelete  cascade             table- gt foreign  city  - gt references  id  - gt on  cities  - gt onDelete  cascade             table- gt foreign  type  - gt references  id  - gt on  property types  - gt onDelete  cascade                 Schema  table  areas   function Blueprint  table             table- gt foreign  city id  - gt references  id  - gt on  cities  - gt onDelete  cascade

User · Answer

This error occurred for me because  - while the table I was trying to create was InnoDB - the foreign table I was trying to relate it to was a MyISAM table

User · Answer

One thing i have noticed is that if the tables use different engine than the foreign key constraint does not work   For example if one table uses    table- gt engine    InnoDB     And the other uses   table- gt engine    MyISAM     would generate an error    SQLSTATE HY000   General error  1215 Cannot add foreign key constraint   You can fix this by just adding InnoDB at the end of your table creation like so   public function up         Schema  create  users   function  Blueprint  table             table- gt bigIncrements  id             table- gt unsignedInteger  business unit id  - gt nullable              table- gt string  name   100             table- gt foreign  business unit id                   - gt references  id                   - gt on  business units                   - gt onDelete  cascade              table- gt timestamps             table- gt softDeletes             table- gt engine    InnoDB      lt     see this line

User · Answer

In my case it did not work until I ran the command  composer dump-autoload   that way you can leave the foreign keys inside the create Schema  public function up                 Schema  create  priorities   function  table             table- gt increments  id   true            table- gt integer  user id             table- gt foreign  user id  - gt references  id  - gt on  users             table- gt string  priority name             table- gt smallInteger  rank             table- gt text  class             table- gt timestamps  timecreated                        Reverse the migrations         return void     public function down                Schema  drop  priorities

User · Answer

In my case  the issue was that the main table already had records in it and I was forcing the new column to not be NULL  So adding a - nullable   to the new column did the trick  In the question s example would be something like this    table- gt integer  user id  - gt unsigned  - gt nullable     or    table- gt unsignedInteger  user id  - gt nullable     Hope this helps somebody

User · Answer

The gist is that the foreign method uses ALTER TABLE to make a pre-existing field into a foreign key  So you have to define the table type before you apply the foreign key  However  it doesn t have to be in a separate Schema   call  You can do both within create  like this   public function up         Schema  create  priorities   function  table             table- gt increments  id   true            table- gt integer  user id  - gt unsigned             table- gt foreign  user id  - gt references  id  - gt on  users             table- gt string  priority name             table- gt smallInteger  rank             table- gt text  class             table- gt timestamps  timecreated                Also note that the type of user id is set to unsigned to match the foreign key

User · Answer

i know thats a old question but make sure if you are working with references the proper supporting engine is defined  set innodb engine for both tables and same data type for the reference columns   table- gt engine    InnoDB

User · Answer

Question already answered  but hope this might help someone else   This error occurred for me because I created the migration table with the foreign key in it firstly before the key existed as a primary key in it s original table  Migrations get executed in the order they were created as indicated by the file name generated after running migrate make  E g  2014 05 10 165709 create student table php   The solution was to rename the file with the foreign key to an earlier time than the file with the primary key as recommended here  http   forumsarchive laravel io viewtopic php id 10246  I think I also had to add in  table- gt engine    InnoDB

User · Answer

In my case I just change the order migrations are executed manually so table users is created first   In folder database migrations  your migration filename have this format  year month day hhmmss create XXXX table php  Just rename create user file so creation date of your table priorities table is set later than user date  even one second later is enough

User · Answer

Be aware  when Laravel sets up a table using   table- gt increments  id      which is standard in most migrations  this will set up an unsigned integer field   Therefore when making a foreign reference from another table to this field  ensure that in the referencing table  you set the field to UnsignedInteger and not  what I d assumed to be an  UnsignedBigInteger field   For example  in the migration file 2018 12 12 123456 create users table php   Schema  create  users   function  Blueprint  table        table- gt increments  id         table- gt string  name         table- gt timestamps      Then in the migration file 2018 12 12 18000000 create permissions table php  which sets up the foreign reference back to users   Schema  create  permissions   function  Blueprint  table        table- gt increments  id         table- gt UnsignedInteger  user id       UnsignedInteger    increments  in users table      table- gt boolean  admin         table- gt boolean  enabled         table- gt timestamps            set up relationship      table- gt foreign  user id  - gt reference  id  - gt on  users  - gt onDelete  cascade

User · Answer

You can directly pass boolean parameter in integer column saying that it should be unsigned or not  In laravel 5 4 following code solved my problem            table- gt integer  user id   false  true     Here second parameter false represents that it should not be auto-incrementing and third parameter true represents that it should be unsigned  You can keep foreign key constraint in same migration or separate it  It works on both

User · Answer

Add it in two steps  and it s good to make it unsigned too   public function up         Schema  create  priorities   function  table             table- gt increments  id   true            table- gt integer  user id  - gt unsigned             table- gt string  priority name             table- gt smallInteger  rank             table- gt text  class             table- gt timestamps  timecreated                Schema  table  priorities   function  table            table- gt foreign  user id  - gt references  id  - gt on  users

User · Answer

For me  the issue was an old table was using MyISAM and not InnoDB  This fixed it       tables              table 1            table 2              foreach   tables as  table             DB  statement  ALTER TABLE      table     ENGINE   InnoDB

User · Answer

Using Laravel 5 3 had the same problem   The solution was to use unsignedInteger instead of integer  name  - unsigned     So this is what worked   table- gt unsignedInt  column name     table- gt foreign  column name  - gt references  id  - gt on  table name      The reason this worked is the fact that when using integer  name  - unsigned the column created in the table had length 11  but when using unsigedInteger  name   the column had length 10    Length 10 is the length for primary keys when using Laravel so the columns length matched

User · Answer

So simple      if your first create  priorities  migration file  Laravel first run  priorities  while  users  table does not exist   how it can add relation to a table that does not exist    Solution  pull out foreign key codes from   priorities  table  your migration file should be like this     and add to a new migration file  here its name is create prioritiesForeignKey table and add these codes   public function up                 Schema  table  priorities   function  Blueprint  table                       table- gt foreign  user id                 - gt references  id                 - gt on  users

User · Answer

For making addition of foreign key constraint in laravel  the following worked for me    Create the column to be foreign key as follows    table- integer  column name  - unsigned    Adding the constraint  line immediately after  1  i e    table- integer  column name  - unsigned     table- foreign  column name  - references  pk of other table  - on  other table

User · Answer

Some times this error may come because of sequence of migrations   Like Users and Order are two tables  Order table have foriegn key of users        During migration if Order table migrate first then it will cause the problem because there is no users to match foreign key   Solution      Just Put your Order Update table under the users for update  Example   In my case Education and University tables Education Table  public function up         Schema  create  doc education   function  Blueprint  table             table- gt increments  id             table- gt integer  uni id  - gt unsigned  - gt nullable             table- gt timestamps                In the University       Schema  create  doc universties   function  Blueprint  table             table- gt increments  id             table- gt string  uni name             table- gt string  location  - gt nullable             table- gt timestamps                           Schema  table  doc education   function Blueprint  table             table- gt foreign  uni id  - gt references  id           - gt on  doc universties  - gt onDelete  cascade

User · Answer

I had this issue with laravel 5 8 and i fixed this code  as shown here in Laravel documentation  to where ever i am adding a foreign key   table- gt unsignedBigInteger  user id     table- gt foreign  user id  - gt references  id  - gt on  users  - gt onDelete  cascade     then i ran   php artisan migrate refresh Since this syntax is rather verbose  Laravel provides additional  terser methods that use convention to provide a better developer experience  The example above could be written like so  Schema  table  posts   function  Blueprint  table         table- gt foreignId  user id  - gt constrained  - gt onDelete  cascade

User · Answer

Laravel  5 8     As of Laravel 5 8  migration stubs use the bigIncrements method on ID   columns by default  Previously  ID columns were created using the   increments method       This will not affect any existing code in your project  however  be   aware that foreign key columns must be of the same type  Therefore  a   column created using the increments method can not reference a column   created using the bigIncrements method       Source  Migrations  amp  bigIncrements     Example  Let s imagine you are building a simple role-based application  and you need to references user id in the PIVOT table  role user    2019 05 05 112458 create users table php            public function up         Schema  create  users   function  Blueprint  table              table- gt bigIncrements  id              table- gt string  full name             table- gt string  email             table- gt timestamps                2019 05 05 120634 create role user pivot table php            public function up         Schema  create  role user   function  Blueprint  table                this line throw QueryException  SQLSTATE HY000   General error  1215 Cannot add foreign key constraint                 table- gt integer  user id  - gt unsigned  - gt index              table- gt bigInteger  user id  - gt unsigned  - gt index       this is working          table- gt foreign  user id  - gt references  id  - gt on  users  - gt onDelete  cascade                As you can see  the commented line will throw a query exception  because  as mentioned in the upgrade notes  foreign key columns must be of the same type   therefore you need to either change the foreing key  in this example it s user id  to bigInteger in role user table or change bigIncrements method to increments method in users table and use the commented line in the pivot table  it s up to you     I hope i was able to clarify this issue to you

User · Answer

For me  the table column that my child table referenced wasn t indexed   Schema  create  schools   function  Blueprint  table         table- gt integer  dcid  - gt index  - gt unque         table- gt integer  school number  - gt index       The important thing is that this is indexed      table- gt string  name         table- gt string  abbreviation         table- gt integer  high grade         table- gt integer  low grade         table- gt timestamps         table- gt primary  dcid         Schema  create  students   function  Blueprint  table           table- gt increments  id           table- gt integer  dcid  - gt index  - gt unique  - gt nullable           table- gt unsignedInteger  student number  - gt nullable           table- gt integer  schoolid  - gt nullable           table- gt foreign  schoolid  - gt references  school number  - gt on  schools  - gt onDelete  set null                       Ignore the terrible naming  it s from another terribly designed system

User · Answer

You should write in this way  public function up         Schema  create  transactions   function  Blueprint  table             table- gt bigIncrements  id             table- gt float  amount   11  2            table- gt enum  transaction type     debit    credit              table- gt bigInteger  customer id  - gt unsigned                   table- gt timestamps                                  Schema  table  transactions   function  table             table- gt foreign  customer id                 - gt references  id  - gt on  customers                 - gt onDelete  cascade                     The foreign key field should be unsigned  hope it helps

User · Answer

Learning english  sorry  I try in my project with  quot foreignId quot  and works  In your code is just delete the column user id and add the foreignId on the reference   public function up          Schema  create  priorities   function  table             table- gt increments  id   true            table- gt foreignId  user id  - gt references  id  - gt on  users             table- gt string  priority name             table- gt smallInteger  rank             table- gt text  class             table- gt timestamps  timecreated               IMPORTANTE  Create first the tables without foreign keys on this case the  quot users quot  table

User · Answer

Another cause could be the DB engine types  Laravel by default uses MyISAM engine  So if you need to enforce foreign key reference checks  you should use InnoDB as your engine  This setting is in your config database php file  Change the engine to  engine    gt   InnoDB  Also if you re trying to do this to an existing table  change the engine by using DB  statement  quot ALTER TABLE    tableName   ENGINE    InnoDB  quot

User · Answer

Chiming in here a few years after the original question  using laravel 5 1  I had the same error as my migrations were computer generated with all the same date code  I went through all the proposed solutions  then refactored to find the error source   In following laracasts  and in reading these posts  I believe the correct answer is similar to Vickies answer  with the exception that you don t need to add a separate schema call  You don t need to set the table to Innodb  I am assuming laravel is now doing that    The migrations simply need to be timed correctly  which means you will modify the date code up  later  in the filename for tables that you need foreign keys on  Alternatively or in addition  Lower the datecode for tables that don t need foreign keys   The advantage in modifying the datecode is your migration code will be easier to read and maintain   So far my code is working by adjusting the time code up to push back migrations that need foreign keys    However I do have hundreds of tables  so at the very end I have one last table for just foreign keys  Just to get things flowing  I am assuming I will pull those into the correct file and modify the datecode as i test them   So an example  file 2016 01 18 999999 create product options table  This one needs the products table to be created  Look at the file names    public function up         Schema  create  product options   function  Blueprint  table             table- gt increments  id             table- gt integer  product attribute id  - gt unsigned  - gt index             table- gt integer  product id  - gt unsigned  - gt index             table- gt string  value   40 - gt default               table- gt timestamps               table- gt foreign  product id  - gt references  id  - gt on  products             table- gt foreign  product attribute id  - gt references  id  - gt on  product attributes             table- gt foreign  product id  - gt references  id  - gt on  products                        Reverse the migrations         return void     public function down         Schema  drop  product options        the products table  this needs to migrate first  2015 01 18 000000 create products table  public function up         Schema  create  products   function  Blueprint  table             table- gt increments  id              table- gt string  style number   64 - gt default               table- gt string  title  - gt default               table- gt text  overview  - gt nullable             table- gt text  description  - gt nullable               table- gt timestamps                      Reverse the migrations         return void     public function down         Schema  drop  products        And finally at the very end the file that I am temporarily using to resolve issues  which I will refactor as I write tests for the models which I named 9999 99 99 999999 create foreign keys php  These keys are commented as I pulled them out  but you get the point       public function up                   Schema  table  product skus   function   table                   table- gt foreign  product id  - gt references  id  - gt on  products  - gt onDelete  cascade                                     Reverse the migrations                 return void             public function down                   Schema  table  product skus   function   table                             table- gt dropForeign  product skus product id foreign

User · Answer

I was having the same issue using Laravel 5 8  After taking a closer look to laravel docs  moreover here Migrations  amp  bigIncrements  The way I solved it is by adding primary keys   table- bigIncrements  id    to every single table that is related to the table  users  and its associations  in my case the table  role   Lastly  I had   table- unsignedBigInteger  for associating roles to users  Many-to-Many   that is  table  role user     1  Users table      Schema  create  users   function  Blueprint  table                 table- gt bigIncrements  id                 table- gt string  name                 table- gt string  email  - gt unique                 table- gt timestamp  email verified at  - gt nullable                 table- gt string  password                 table- gt rememberToken                 table- gt timestamps                 2  Roles Table     Schema  create  roles   function  Blueprint  table             table- gt bigIncrements  id             table- gt string  name  - gt unique             table- gt string  display name  - gt nullable             table- gt string  description  - gt nullable             table- gt timestamps             3  Table role user Schema  create  role user   function  Blueprint  table                 table- gt unsignedBigInteger  user id                 table- gt unsignedBigInteger  role id                 table- gt foreign  user id  - gt references  id  - gt on  users                   - gt onUpdate  cascade  - gt onDelete  cascade                 table- gt foreign  role id  - gt references  id  - gt on  roles                   - gt onUpdate  cascade  - gt onDelete  cascade                 table- gt primary   user id    role id

User · Answer

In laravel 5 8  the users table uses bigIncrements  id   data type for the primary key  So that when you want to refer a foreign key constraint your user id column needs to be unsignedBigInteger  user id   type

User · Answer

In my case  I was referencing an integer id column on a string user id column   I changed    table- gt string  user id    to    table- gt integer  user id  - gt unsigned     Hope it helps someone

User · Answer

I think  the reference key must be  index   for example  down   public function up         Schema  create  clicks   function  Blueprint  table             table- gt increments  id             table- gt string  viewer id             table- gt integer  link id  - gt index  - gt unsigned             table- gt string  time             table- gt timestamps                 Schema  table  clicks   function  table             table- gt foreign  link id  - gt references  id  - gt on  links  - gt onDelete  cascade                  good luck

[laravel] Migration: Cannot add foreign key constraint

Examples related to laravel

Examples related to laravel-4

Examples related to foreign-keys

Examples related to migration

Examples related to eloquent