I have just moved from mysql to MSSQL server. But, if I try to run migration I received error as follows.
[Microsoft][ODBC Driver 13 for SQL Server][SQL Server]Introducing FOREIGN KEY constraint 'my_list_user_user_id_foreign' on table 'my_l ist_user' 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 "my_list_user" add constrai nt "my_list_user_user_id_foreign" foreign key ("user_id") references "users" ("id") on delete cascade)
My migration schema looks lie belows
Users
Schema::create('users', function (Blueprint $table) {
$table->increments('id');
$table->string('name');
$table->string('email')->unique();
$table->string('password');
$table->integer('organization_id')->unsigned();
$table->foreign('organization_id')->references('id')->on('organizations');
$table->rememberToken();
$table->softDeletes();
$table->timestamps();
});
My_Lists
Schema::create('my_lists', function (Blueprint $table) {
$table->increments('id');
$table->string('title',255);
$table->text('description');
$table->unsignedInteger('created_by');
$table->foreign('created_by')->references('id')->on('users')->onDelete('cascade');
$table->softDeletes();
$table->timestamps();
});
My_list_user
Schema::create('my_list_user', function (Blueprint $table) {
$table->integer('my_list_id')->unsigned()->index();
$table->foreign('my_list_id')->references('id')->on('my_lists')->onDelete('cascade');
$table->integer('user_id')->unsigned()->index();
$table->foreign('user_id')->references('id')->on('users')->onDelete('cascade');
$table->primary(['my_list_id', 'user_id']);
});
Here, created by means the one who created the list and user_id from pivot tables refers to the users who have access to the list.
Is this some thing I am making over comlpexed? How can I overcome this problem with MS SQL?
Is that MS SQL does not support even a single level of cascade? How can I solve this?