0

I want to create relationship between user and budget. There are 2 foreignkey in budget table.

  1. requestor_id
  2. approver_id

All is linked to user table.

If record in table users is deleted. I want to delete budget table also, if requestor or approver using that deleted record.

here is my migration

users table :

public function up()
{
    Schema::create('users', function (Blueprint $table) {
        $table->id();
        $table->string('name');
        $table->string('email')->unique();
        $table->timestamp('email_verified_at')->nullable();
        $table->string('password');
        $table->rememberToken();
        $table->timestamps();
    });
}

Budgets table:

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

        $table->foreignId('requestor_id')->nullable()
            ->references('id')->on('users')
            ->cascadeOnDelete();

        $table->foreignId('approver_id')->nullable()
            ->references('id')->on('users')
            ->cascadeOnDelete();

        $table->timestamps();
    });
}

But when I tried to migration, I got error :

SQLSTATE[42000]: [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Introducing FOREIGN KEY constraint 'budgets_approver_id_foreign' on table 'budgets' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints. (SQL: alter table "budgets" add constraint "budgets_approver_id_foreign" foreign key ("approver_id") references "users" ("id") on delete cascade)

How I can fix this error ?

Note: I am using SQL-Server 2018

Thank you

Altimuksin
  • 67
  • 5
  • It think the problem is from the Database Itself not in the code. Since you are using MSSQL it might be helpful https://learn.microsoft.com/en-US/sql/relational-databases/errors-events/mssqlserver-1785-database-engine-error?view=sql-server-ver16 – xenooooo Jan 09 '23 at 04:43
  • Also here https://stackoverflow.com/questions/851625/foreign-key-constraint-may-cause-cycles-or-multiple-cascade-paths – xenooooo Jan 09 '23 at 04:43
  • base on learn.microsoft.com/en-US/sql/relational-databases/…. ALTER TABLE table2 ADD CONSTRAINT fk_two FOREIGN KEY (addedby) REFERENCES table1 (user_ID) ON DELETE NO ACTION ON UPDATE NO ACTION GO It will only NO ACTION if user_ID on table is deleted – Altimuksin Jan 09 '23 at 06:18

2 Answers2

0

your budgets migration file content is incorrect, try:

    public function up()
    {
        Schema::create('budgets', function (Blueprint $table) {
            $table->id();
            $table->unsignedBigInteger('requestor_id');
            $table->unsignedBigInteger('approver_id');
            
            $table->foreign('requestor_id')->references('id')->on('users')->onDelete('cascade')->onUpdate('cascade');
            $table->foreign('approver_id')->references('id')->on('users')->onDelete('cascade')->onUpdate('cascade');
    
    
            $table->timestamps();
        });
    }
Ehsan Faramarz
  • 177
  • 5
  • Hi Ehsan, same error is occured – Altimuksin Jan 09 '23 at 04:39
  • it should not be the same error please can you copy and paste the error for me, make sure that you didn't have the `budgets` table when you run `php artisan migrate` – Ehsan Faramarz Jan 09 '23 at 04:48
  • SQLSTATE[42000]: [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Introducing FOREIGN KEY constraint 'budgets_approver_id_foreign' on table 'budgets' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints. (SQL: alter table "budgets" add constraint "budgets_approver_id_foreign" foreign key ("approver_id") references "users" ("id") on delete cascade on update cascade) – Altimuksin Jan 09 '23 at 05:56
  • Its not the same error bro, it seems that you're trying to creat an foreign key that is already exists on the table which means you already have a foreign path for `approver_id` to fix this issue first, delete your `budgets` table and run your `php artisan migrate` again to create the table again or simply put this two line on your migration file `down()` method: `$table->dropForeign(['approver_id']);` `$table->dropColumn('approver_id'); });` then run the command again your problem should be fixed – Ehsan Faramarz Jan 09 '23 at 20:09
0

It would work

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

        $table->foreignId('requestor_id')->nullable()
            ->constrained()->onDelete('cascade');

        $table->foreignId('approver_id')->nullable()
            ->constrained()->onDelete('cascade');

        $table->timestamps();
    });
}
eglease
  • 2,445
  • 11
  • 18
  • 28
Hasan Aftab
  • 1
  • 4