How to make a foreign key within two columns is the same table?-CodePudding

I have a database called laravelpro and in it there is a table called categories. Here is the migration that makes the categories table.

Schema::create('categories', function (Blueprint $table) {
  $table->id();

  $table->string('name');
  $table->unsignedBigInteger('parent')->default(0);
  $table->foreign('parent')->references('id')->on('categories')->onDelete('cascade');

  $table->timestamps();
});

In categories table, a category can be another category's parent(I'm doing this with the parent field). As you can see there is a line in migration that is making a foreign key from the parent field to the id field. The purpose of doing this action is that I want to remove all subcategories of a category after I remove the parent category and so on ... I ran the migration with no errors but when I want to add a category, I will face his error:

SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails 
(`laravelpro`.`categories`, CONSTRAINT `categories_parent_foreign` FOREIGN KEY (`parent`) REFERENCES `categories` (`id`) ON DELETE CASCADE)
INSERT INTO `categories` (`name`, `parent`, `updated_at`, `created_at`) VALUES (test, 0, 2022-10-06 20:33:41, 2022-10-06 20:33:41)

I searched a lot and didn't find anything related to my subject. Any help would be appreciated...

CodePudding user response：

Make parent nullable.

Schema::create('categories', function (Blueprint $table) {
  $table->id();

  $table->string('name');
  $table->unsignedBigInteger('parent')->nullable();
  $table->foreign('parent')->references('id')->on('categories')->onDelete('cascade');

  $table->timestamps();
});