juin
2010
Un article de zinzineti
Comment faire des suppressions ou des modifications en cascade sur des tables PARENT-ENFANT dont les liens de parenté sont des clés ?
/*********************************************************************************************
DESCRIPTION : MODIFIER LA TABLE ENFANT (TABLE AYANT UNE CLE ETRANGERE) POUR REALISER DES SUPPRESSIONS EN CASCADE
Auteur : Etienne ZINZINDOHOUE
***************************************************************************************************************/
–> 1.) TABLE PARENT : HumanResources.Department
(
DepartmentID SMALLINT IDENTITY(1,1) NOT NULL,
Name NAME NOT NULL,
GroupName NAME NOT NULL,
ModifiedDate DATETIME DEFAULT (getdate()) NOT NULL,
CONSTRAINT PK_Department_DepartmentID PRIMARY KEY CLUSTERED ( DepartmentID )
)
ON [PRIMARY]
–> 2.) TABLE ENFANT : HumanResources.EmployeeDepartmentHistory
(
EmployeeID INT NOT NULL,
DepartmentID SMALLINT NOT NULL,
ShiftID TINYINT NOT NULL,
StartDate DATETIME NOT NULL,
EndDate DATETIME,
ModifiedDate DATETIME DEFAULT (getdate()) NOT NULL,
CONSTRAINT CK_EmployeeDepartmentHistory_EndDate CHECK ([EndDate]>=[StartDate] OR [EndDate] IS NULL),
CONSTRAINT PK_EmployeeDepartmentHistory_EmployeeID_StartDate_DepartmentID PRIMARY KEY CLUSTERED ( EmployeeID,StartDate,DepartmentID,ShiftID )
)
ON [PRIMARY]
/*-------- Foreign Keys-----------------*/
ALTER TABLE HumanResources.EmployeeDepartmentHistory
ADD CONSTRAINT FK_EmployeeDepartmentHistory_Department_DepartmentID FOREIGN KEY( DepartmentID )
REFERENCES [HumanResources].[Department] ( DepartmentID )
ON UPDATE NO ACTION
ON DELETE NO ACTION;
ALTER TABLE HumanResources.EmployeeDepartmentHistory
ADD CONSTRAINT FK_EmployeeDepartmentHistory_Employee_EmployeeID FOREIGN KEY( EmployeeID )
REFERENCES [HumanResources].[Employee] ( EmployeeID )
ON UPDATE NO ACTION
ON DELETE NO ACTION;
ALTER TABLE HumanResources.EmployeeDepartmentHistory
ADD CONSTRAINT FK_EmployeeDepartmentHistory_Shift_ShiftID FOREIGN KEY( ShiftID )
REFERENCES [HumanResources].[Shift] ( ShiftID )
ON UPDATE NO ACTION
ON DELETE NO ACTION;
–> 3.) DELETE dans la table PARENT HumanResources.Department
WHERE Name = 'Sales'
–> 4.) RESULTAT :
*ERREUR : L’instruction DELETE est en conflit avec la contrainte REFERENCE « FK_EmployeeDepartmentHistory_Department_DepartmentID ». Le conflit s’est produit dans la base de données « AdventureWorks », table « HumanResources.EmployeeDepartmentHistory », column ‘DepartmentID’.L’instruction a été arrêtée.
–> 5.) SOLUTION : Modifier la table ENFANT HumanResources.EmployeeDepartmentHistory en ajoutant l’option
ON DELETE CASCADE sur la clé étrangère « FK_EmployeeDepartmentHistory_Department_DepartmentID ».
ALTER TABLE HumanResources.EmployeeDepartmentHistory ADD CONSTRAINT FK_EmployeeDepartmentHistory_Department_DepartmentID FOREIGN KEY( DepartmentID )
REFERENCES [HumanResources].[Department] ( DepartmentID )
ON DELETE CASCADE;
–> 6.) TEST DELETE CASCADE
WHERE Name = 'Sales'
–> 7.) RESULTAT : Le delete en cascade s’est réalisé avec succès dans les tables concernées.
–> 8.) CONCLUSION :
ON DELETE CASCADE permet de réaliser des suppression en cascade. De la même façon l’option ON UPDATE CASCADE permet de réaliser également des mis à jour en cascade
/***************************************************************************************************************
DESCRIPTION : MODIFIER LA TABLE ENFANT (TABLE AYANT UNE CLE ETRANGERE) POUR REALISER DES SUPPRESSIONS EN CASCADE
Auteur : Etienne ZINZINDOHOUE
***************************************************************************************************************/
Voici l’erreur qui remonte
Msg 4902, Niveau 16, État 1, Ligne 1
L’objet « dbo.Comptes » est introuvable, car il n’existe pas ou vous ne disposez pas des autorisations requises.
salut à tous,
J’ai exactement le même genre de soucis mais je ne comprends pas d’ou vient mon erreur,
—————————————————
Un problème s’est produit lors de la tentative de suppression de la ligne 3
Source d’erreur : .Net SqlClient Data Provider
Message d’erreur : L’instruction DELETE est en conflit avec la contrainte REFERENCE « FK_TypeCompleClientCompteClient ». Le conflit s’est produit dans la base de données « CERPS », table « dbo.Comptes », Column ‘TypeCompteId ».
L’instruction a été arrêtée.
————————————————————
j’ai donc suivi vos conseil et j’ai essayé de modifier la table enfant en ajoutant l’option ON DELETE CASCADE sur la clé étrangère.
FK_TypeCompleClientCompteClient
voici la syntaxe que j’ai utilisé
ALTER TABLE dbo.Comptes DROP CONSTRAINT FK_TypeCompteClientCompteClient_Comptes_TypeCompteId
ALTER TABLE dbo.Comptes ADD CONSTRAINT FK_TypeCompteClientCompteClient_Comptes_TypeCompteId FOREIGN KEY( TypeCompteId )
REFERENCES [Compte].[TypeCompte] ( TypeCompteId )
ON DELETE CASCADE;
Je ne comprends pas mon erreur.
Si jamais vous arriviez à m’indiquer mon erreur,
D’avance merci
Lut,
Bien qu’il soit pratique d’utiliser ON UPDATE et ON DELETE, il faut quand même mettre un bémole à son utilisation je pense. En effet, sur les tables à volumétrie importante, cela peut engendrer des problèmes de performances considérables …
Il vaut mieux implémenter dans ce cas, un job de suppression des données pendant une période creuse ..
A+