Laravel migration error SQLSTATE[42000]

Question

I have a couple of Laravel migrations.

1-create_countries_table`

Schema::create('countries', function(Blueprint $table)
{
    $table->increments('id');
        $table->string('name');
    $table->timestamps();
});

2-create_cities_table

Schema::create('cities', function(Blueprint $table)
{
    $table->increments('id');
        $table->string('name');
        $table->smallInteger('country_id');
    $table->timestamps();
        $table->foreign('country_id')->references('id')->on('countries');
});

When I use php artisan migrate , I see this error

  [Illuminate\Database\QueryException]
  SQLSTATE[HY000]: General error: 1215 Cannot add foreign key constraint
 (SQL: alter table `cities` add constraint cities_country_id_foreign
 foreign key (`country_id`) references `countries` (`id`))

  [PDOException]
  SQLSTATE[HY000]: General error: 1215 Cannot add foreign key constraint

What is the problem?

Shouldn't `refrences('id')` be `references('id')`? – lmz May 05 '15 at 15:54 — lmz, May 05 '15 at 15:54

score 1 · Accepted Answer · answered May 05 '15 at 15:54

1

Try $table->integer('country_id')->unsigned(); instead of $table->smallInteger('country_id');

answered May 05 '15 at 15:54

Pawel Bieszczad

12,925
3
37
40

I do that but i have the same problem. I think the problem cause is laravel generate mysql query that dose not accept from mysql query syntax. – Bijan Mohammadpoor May 05 '15 at 16:10
It should generate the right syntax if you selected the right driver in config files (mysql is the default I believe). Your error message seems to have a space after countries, double check to make sure its not the case in the migration. – Pawel Bieszczad May 05 '15 at 16:24
I checked it out . and change refrence to references but steel i have problem. – Bijan Mohammadpoor May 05 '15 at 19:44
Did you figure it out? what was the problem? – Pawel Bieszczad May 05 '15 at 20:15

score 1 · Answer 2 · answered Sep 04 '18 at 06:13

1

solution for this problem.
add to app/Providers/AppServiceProvider.php

use Illuminate\Support\Facades\Schema;

public function boot(){
    Schema::defaultStringLength(191);
}

https://laravel-news.com/laravel-5-4-key-too-long-error

answered Sep 04 '18 at 06:13

Rodia

11
3

score 0 · Answer 3 · answered May 05 '15 at 20:16

In the laravel when use $table->increments('id'); Laravel create a int(10) field. to connect a field to an id as foregin key it must be int(10). for make a foreign key int 10 we muste use this code:

$table->integer('country_id')->unsigned();

if dont use unsigned(); method, a field will create by int(11) type and isn't mach to int(10)

Laravel migration error SQLSTATE[42000]

3 Answers3