octobre
2010
Un article de zinzineti
Il arrive souvent que le modèle de données d’une base SQL SERVER ne prévoit pas la suppression en cascade ou la mise à jour en cascade entre les tables PARENT-ENFANT. Pour effectuer donc un DELETE ou un UPDATE dans la table PARENT il faut d’abord faire en amont la même opération dans toutes les tables ENFANTS. Comment faire donc un DELETE ou UPDATE en cascade sans toucher au modèle de données et sans trigger ?
Nous allons utiliser la base AdventureWorks et les tables :
– HumanResources.Department
– HumanResources.EmployeeDepartmentHistory
voici le script de création de ces tables :
CREATE TABLE 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]
-- Table HumanResources.EmployeeDepartmentHistory
CREATE TABLE 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;
1. ) Erreur générée lors du DELETE aveugle dans la table HumanResources.Department
Quand on exécute directement la commande :
WHERE GroupName = 'Research and Development'
On a cette 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.
2. ) La solution revient à faire d’abord le DELETE approprié dans la table ENFANT HumanResources.EmployeeDepartmentHistory avant la suppression dans la table PARENT HumanResources.Department
Voici le script T-SQL pour cette opération :
BEGIN TRANSACTION
--> DELETE DE LA TABLE ENFANT
DELETE FROM HumanResources.EmployeeDepartmentHistory
WHERE HumanResources.EmployeeDepartmentHistory.DepartmentID
IN
(SELECT Department.DepartmentID
FROM HumanResources.Department Department INNER JOIN HumanResources.EmployeeDepartmentHistory EmployeeDepartmentHistory
ON Department.DepartmentID = EmployeeDepartmentHistory.DepartmentID
WHERE Department.GroupName = 'Research and Development'
)
--> DELETE TABLE PARENT
DELETE FROM HumanResources.Department
WHERE GroupName = 'Research and Development'
COMMIT
*********************************************************
Auteur : Etienne ZINZINDOHOUE
*********************************************************