MySQL error "Foreign key constraint is incorrectly formed"

Question

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();
    });
}

You don't even have an FK constraint shown in that one table schema with no relation — Drew, Dec 14 '15 at 09:11

score 2 · Accepted Answer · edited May 23 '17 at 11:59

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.

MySQL error "Foreign key constraint is incorrectly formed"

1 Answers1