From f9eba12312fc6a1758f7f1051a8f30b435175f94 Mon Sep 17 00:00:00 2001 From: Dan McGee 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 Signed-off-by: Lukas Fleischer --- support/schema/aur-schema.sql | 5 +++-- 1 file changed, 3 insertions(+), 2 deletions(-) (limited to 'support') diff --git a/support/schema/aur-schema.sql b/support/schema/aur-schema.sql index 44501b6b..8226b182 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-24-g4f1b