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
– lmz

2015-05-05 15:54:40 +00:00
Commented May 5, 2015 at 15:54 — lmz
– lmz, Commented May 5, 2015 at 15:54

Pawel Bieszczad · Accepted Answer · 2015-05-05 15:54:08Z

1

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

answered May 5, 2015 at 15:54

Pawel Bieszczad

13.4k3 gold badges39 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Bijan Mohammadpoor Over a year ago

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.

Pawel Bieszczad Over a year ago

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.

Bijan Mohammadpoor Over a year ago

I checked it out . and change refrence to references but steel i have problem.

Pawel Bieszczad Over a year ago

Did you figure it out? what was the problem?

Rodia · Accepted Answer · 2018-09-04 06:13:13Z

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

Bijan Mohammadpoor · Accepted Answer · 2015-05-05 20:16:13Z

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)

Collectives™ on Stack Overflow

Laravel migration error SQLSTATE[42000]

3 Answers 3

4 Comments

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

Comments

Comments

Related