Ad

MySQL Error "Foreign Key Constraint Is Incorrectly Formed"

- 1 answer

I am having trouble to solve this, I have this create statement:

create table `roles` 
(
    `name` varchar(255) not null,
    `description` varchar(255) not null, 
    `created_at` timestamp default 0 not null, 
    `updated_at` timestamp default 0 not null
) default character set utf8 collate utf8_unicode_ci;

But I am getting error:

Foreign key constraint is incorrectly formed

Can anybody suggest what I am doing wrong here ?

FYI, I am using MySQL.

Actual statement is run using Laravel migration which gives error:

public function up()
{
    Schema::create('roles', function(Blueprint $table)
    {
        $table->string('name')->unique()->primary();
        $table->string('description');
        $table->timestamps();
    });
}
Ad

Answer

I have taken a quick look around and came across this SO question that does a good job of explaining why this error may occur: mysql Foreign key constraint is incorrectly formed error

What does this mean for you? It means that you have a foreign key field somewhere that is expecting to be populated with the primary key from your roles table, but the types between the two differ. I would imagine that in another table somewhere you have a column such as role_id that is int, bigint, tinyint, etc. Either way, you shouldn't be using strings as primary keys.

Ad
source: stackoverflow.com
Ad