From f9eba12312fc6a1758f7f1051a8f30b435175f94 Mon Sep 17 00:00:00 2001
From: Dan McGee <dan@archlinux.org>
Date: Tue, 1 Mar 2011 10:21:06 -0600
Subject: Ensure users can be deleted when foreign keys are present

This change is necessary to prevent this:
    mysql> delete from Users where ID = 112;
    ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`aur`.`Packages`, CONSTRAINT `Packages_ibfk_2` FOREIGN KEY (`SubmitterUID`) REFERENCES `Users` (`ID`) ON DELETE NO ACTION)

As a bonus, due to foreign keys, orphaning of packages will be
automatic.

Signed-off-by: Dan McGee <dan@archlinux.org>
Signed-off-by: Lukas Fleischer <archlinux@cryptocrack.de>
---
 support/schema/aur-schema.sql | 5 +++--
 1 file changed, 3 insertions(+), 2 deletions(-)

(limited to 'support/schema/aur-schema.sql')

diff --git a/support/schema/aur-schema.sql b/support/schema/aur-schema.sql
index 44501b6..8226b18 100644
--- a/support/schema/aur-schema.sql
+++ b/support/schema/aur-schema.sql
@@ -114,8 +114,9 @@ CREATE TABLE Packages (
 	INDEX (SubmitterUID),
 	INDEX (MaintainerUID),
 	FOREIGN KEY (CategoryID) REFERENCES PackageCategories(ID) ON DELETE NO ACTION,
-	FOREIGN KEY (SubmitterUID) REFERENCES Users(ID) ON DELETE NO ACTION,
-	FOREIGN KEY (MaintainerUID) REFERENCES Users(ID) ON DELETE NO ACTION
+	-- deleting a user will cause packages to be orphaned, not deleted
+	FOREIGN KEY (SubmitterUID) REFERENCES Users(ID) ON DELETE SET NULL,
+	FOREIGN KEY (MaintainerUID) REFERENCES Users(ID) ON DELETE SET NULL
 ) ENGINE = InnoDB;
 
 
-- 
cgit v1.2.3-54-g00ecf

