Fix: #5329 - SQL-Server cannot handle complicated foreign keys

2 files changed, 16 insertions, 2 deletions

diff --git a/app/Schema/Migration45.php b/app/Schema/Migration45.php
index c1c84f31f1..b8e7a6e774 100644
--- a/app/Schema/Migration45.php
+++ b/app/Schema/Migration45.php
@@ -49,8 +49,15 @@ final readonly class Migration45 implements MigrationInterface
                 $table->integer(column: 'private')->default(value: 0)->index();
                 $table->integer(column: 'contact_user_id')->nullable()->index();
                 $table->integer(column: 'support_user_id')->nullable()->index();
-                $table->foreign(columns: ['contact_user_id'])->references(['user_id'])->on('user')->nullOnDelete()->cascadeOnUpdate();
-                $table->foreign(columns: ['support_user_id'])->references(['user_id'])->on('user')->nullOnDelete()->cascadeOnUpdate();
+
+                if (DB::driverName() === DB::SQL_SERVER) {
+                    // SQL-Server can't use CASCADE or SET NULL constraints here, as it can't handle multiple paths
+                    $table->foreign(columns: ['contact_user_id'])->references(['user_id'])->on('user');
+                    $table->foreign(columns: ['support_user_id'])->references(['user_id'])->on('user');
+                } else {
+                    $table->foreign(columns: ['contact_user_id'])->references(['user_id'])->on('user')->nullOnDelete()->cascadeOnUpdate();
+                    $table->foreign(columns: ['support_user_id'])->references(['user_id'])->on('user')->nullOnDelete()->cascadeOnUpdate();
+                }
             });
         }
 
diff --git a/app/Services/UserService.php b/app/Services/UserService.php
index e51af70596..fb691fc90a 100644
--- a/app/Services/UserService.php
+++ b/app/Services/UserService.php
@@ -310,6 +310,13 @@ class UserService
         DB::table('user_gedcom_setting')->where('user_id', '=', $user->id())->delete();
         DB::table('user_setting')->where('user_id', '=', $user->id())->delete();
         DB::table('message')->where('user_id', '=', $user->id())->delete();
+
+        if (DB::driverName() === DB::SQL_SERVER) {
+            // SQL-Server cannot handle these foreign key constraints.
+            DB::table('gedcom')->where('contact_user_id', '=', $user->id())->update(['contact_user_id' => null]);
+            DB::table('gedcom')->where('support_user_id', '=', $user->id())->update(['support_user_id' => null]);
+        }
+
         DB::table('user')->where('user_id', '=', $user->id())->delete();
     }