Laravel 7.x - Reflexive Relationship - 'Cannot delete or update a parent row' even with proper constraints

Viewed 71

Hello,

I've got a table representing folders, referencing parent's folder's 'number' (!= ID). It's a reflexive relationship on a table named 'folders'.

IDs are 1, 2, 3, 4, ... and can not be edited.

'folder_number' column contains values like '2048759', '5789516' and can be edited by an admin.

Example: Folders can have a parent folder. (One) Folders can have childs folders (0 or One or Many) A folder have it's parent's folder number stored in the 'parent_folder_number' column.

I want:

When a user (administrator) update the 'folder_number' on a Folder, I want all child-folders to update their 'parent_folder_number' field.

But(t):

I'm having troubles with the FK constraints, when Updating a parent row.

So here's the migration where I create the table:

Schema::create('folders', function (Blueprint $table)
{
[...]
    $table->id();
    $table->bigInteger('folder_number')
          ->unsigned()
          ->index()
          ->onDelete('SET NULL')
          ->onUpdate('CASCADE');
    $table->bigInteger('parent_folder_number')
          ->unsigned()
          ->nullable();
[...]
});

In another migration I've added the following constraint for my FK :

Schema::table('folders', function(Blueprint $table)
{
[...]
    $table->foreign('parent_folder_number')
         ->onDelete('SET NULL')
         ->onUpdate('CASCADE')
         ->references('folder_number')->on('folders');
[...]
});

Everything should work, but when I add a parent folder to a folder and then want to change the number of this parent, this is what happen :

SQLSTATE[23000]: Integrity constraint violation: 1451 Cannot delete or update a parent row: a foreign key constraint fails (`db`.`folders`, CONSTRAINT `folders_parent_folder_number_foreign` FOREIGN KEY (`parent_folder_number`) REFERENCES `folders` (`number`) ON DELETE SET NULL ON UPDATE CASCADE) (SQL: update `folders` set `number` = 1 where `id` = 9)

Can someone help me figure it out ? Thank you :D

1 Answers

I've tried re-reading the doc and to do some fixes, and I'm still encountering issues.

If someone have a solution that doesn't include to do a pivot table, i'm interested !

Related