ForeignUlid: Failed to add the foreign key constraint. Missing index for constraint

I have the following migration code:

$table->ulid('id')->primary();
$table->string('name');
$table->foreignUlid('parent_id')->nullable()->constrained(table: 'folders')->onDelete('cascade');
$table->timestamps();

the id field is clearly indexed as the primary key. But I'm getting the following error when I run the migration:

SQLSTATE[HY000]: General error: 1822 Failed to add the foreign key constraint. Missing index for constraint 'folders_parent_id_foreign' in the referenced table 'folders' (SQL: alter table `folders` add constraint `folders_parent_id_foreign` foreign key (`parent_id`) references `folders` (`id`) on delete cascade)

tisuchi

2 years ago

Level 70

@lyndon Can you try this?

$table->ulid('id')->primary();
$table->string('name');
$table->foreignId('parent_id')->nullable()->constrained('folders')->onDelete('cascade');
$table->timestamps();

lyndon

2 years ago

Level 2

@tisuchi I got this error after trying that... the data types are not the same:

SQLSTATE[HY000]: General error: 3780 Referencing column 'parent_id' and referenced column 'id' in foreign key constraint 'folders_parent_id_foreign' are incompatible. (SQL: alter table `folders` add constraint `folders_parent_id_foreign` foreign key (`parent_id`) references `folders` (`id`) on delete cascade)

porfiro

2 years ago

Level 1

@lyndon Friend, look at the table, the Ulid value needs to come first.

table folders:

$table->ulid()->primary();

lyndon

2 years ago

Best Answer

Level 2

I found the issue, the foreign key cannot be added without creating the table first. I had to add the foreign key in a separate Schema.

public function up()
    {
        Schema::create('folders', function (Blueprint $table) {
            $table->ulid('id')->primary();
			$table->foreignUlid('tenant_id')->constrained()->onDelete('cascade');
			$table->string('name');
            $table->timestamps();
        });

		Schema::table('folders', function (Blueprint $table): void {
			$table->foreignUlid('parent_id')->nullable()->constrained('folders')->onDelete('cascade');
		});
    }

milenial

2 years ago

Level 1

@lyndon hi! If you do like this it will work.

public function up(): void
{
    Schema::create('channel_web_widget', function (Blueprint $table) {
        $table->id();
        $table->foreignUlid('channel_ulid')->index();
        $table->timestamps();
                   
        $table->foreign('channel_ulid')->references('ulid')->on('channels')->onDelete('cascade');
    });  
}

public function up(): void
{
    Schema::create('channel_web_widget', function (Blueprint $table) {
        $table->id();
        $table->foreignUlid('channel_ulid')->constrained('channels','ulid')->onDelete('cascade');
        $table->timestamps();
    });  
}

Please or to participate in this conversation.