Gérer les Foreign Keys – Azure Data Factory
1 - Contexte
Nous somme dans un modèle classique d’une raffinerie de données (ou modèle en médaillons) :
Dans notre exemple, des clés étrangères ont été définies dans les tables du Data Mart. Un ensemble de pipelines Azure Data Factory (ETL) fait transiter les données jusqu’à alimenter les tables du Data Mart. À chaque nouvelle ingestion, il est demandé de supprimer les anciennes données obsolètes. Cependant, lorsqu’on supprime une table étant une référence (FK) dans une autre table, une erreur apparaît :
The DELETE statement conflicted with the REFERENCE constraint "FK_ExampleTable_ReferencedTable".
The conflict occurred in database "YourDatabase", table "ReferencedTable", column 'ForeignKeyColumn'.Remarque : On utilise ici le SGBDR Azure SQL DataBase basé sur SQL Serveur.
2 - Explications et raisonnement
2.1 - Cascade
La première idée serait de définir dans le SQL des FK avec l’option CASCADE :
CREATE TABLE parent (
id INT PRIMARY KEY
);
CREATE TABLE enfant (
id INT PRIMARY KEY,
parent_id INT,
FOREIGN KEY (parent_id) REFERENCES parent(id) ON DELETE CASCADE
);
Ainsi lorsqu’on fera :
DELETE FROM parent WHERE id = 1;
Tous les enregistrements dans la table enfant qui référencent le parent avec id = 1 seront automatiquement supprimés.
Il peut ne pas être toujours souhaitable d’utiliser la suppression en cascade automatique. Par exemple, si nous avons une table avec des clés étrangères (FK) et que, pour des raisons de praticité, notre pipeline supprime la table parent avant de la re-populer, cela entraînera également la suppression de toutes les entrées dans la table enfant en raison des contraintes de suppression en cascade. Ce comportement pourrait poser problème si la table enfant n’est pas nécessairement rechargée ou si elle doit conserver certaines données indépendamment des modifications apportées à la table parent.
2.2 - Contraintes différées
En prenant le script SQL expliqué dans la partie suivante (Partie 3) on remarque la présence de colonnes « is_deferrable » et « initially_deferred » dans la requête. Ces colonnes sont des flags associées à chacune des contraintes reliées à une table : ce sont des attributs liés aux contraintes d’intégrité, particulièrement aux contraintes de clé étrangère dans les systèmes de gestion de bases de données relationnelles (SGBDR). Ils permettent de contrôler le moment où les contraintes sont vérifiées durant une transaction.
is_deferrable(YES/NO) indique si une contrainte est différable ou non. Une contrainte différable peut être différée jusqu’à la fin de la transaction, ce qui permet de retarder sa vérification jusqu’à la fin de la transaction plutôt que de la vérifier immédiatement après chaque modification.initially_deferredindique si une contrainte différable est vérifiée immédiatement ou à la fin de la transaction. Si la contrainte estinitially_deferred, elle est vérifiée à la fin de la transaction, même si elle est différable.
Il nous reste donc à passer ces deux flag à YES et le tour est joué:
-- A la création
CREATE TABLE parent_table (
id SERIAL PRIMARY KEY
);
CREATE TABLE child_table (
id SERIAL PRIMARY KEY,
parent_id INT,
CONSTRAINT fk_parent FOREIGN KEY (parent_id)
REFERENCES parent_table(id)
DEFERRABLE INITIALLY DEFERRED
);
-- En modification
ALTER TABLE child_table
ALTER CONSTRAINT fk_parent
DEFERRABLE INITIALLY DEFERRED;
Msg 102, Level 15, State 1, Line 6
Incorrect syntax near 'DEFERRABLE'.
Malheureusement ces scripts renvoient une erreur.
En effet ces flags ne sont pris en charge que dans Postgresql, tandis que d’autres SGBDR comme Oracle, SQL Server, et MySQL ont des approches différentes ou ne supportent pas directement ces concepts.
3 - Solution
La solution réside dans les scripts de pré-SQL et post-SQL. La version bulldozer consiste à entrer ceci dans la partie SINK de notre DataFlow :
-- Script Pré-SQL
array('ALTER TABLE [schema].[table_enfant_1] NOCHECK CONSTRAINT ALL;',
'ALTER TABLE [schema].[table_enfant_2] NOCHECK CONSTRAINT ALL;',
'DELETE FROM [schema].[table_parent];')
-- Script Post-SQL
array('ALTER TABLE [schema].[table_enfant_1] WITH CHECK CHECK CONSTRAINT ALL;',
'ALTER TABLE [schema].[table_enfant_2] WITH CHECK CHECK CONSTRAINT ALL;')
Ainsi on lève toutes les contraintes des tables reliées à notre table parente avant d’en supprimer les données, puis on les remet en place.
Si on veut être plus fin (ou qu’il y a des exécutions de pipelines parallèles pouvant entraîner des conflits) on peut cibler la contrainte qui nous gène en particulier. Pour identifier notre contrainte de FK soit elle à été nommée en amont :
CREATE TABLE [nom_de_table] (
[colonne1] [type_donnees],
[colonne2] [type_donnees],
-- Autres colonnes
CONSTRAINT [nom_contrainte_fk] FOREIGN KEY ([colonne_fk])
REFERENCES [table_reference] ([colonne_reference])
);
-- Exemple :
-- Création de la table Customers
CREATE TABLE Customers (
CustomerID INT PRIMARY KEY,
CustomerName NVARCHAR(100)
);
-- Création de la table Orders avec une clé étrangère nommée
CREATE TABLE Orders (
OrderID INT PRIMARY KEY,
OrderDate DATE,
CustomerID INT,
CONSTRAINT FK_Customer_Order FOREIGN KEY (CustomerID)
REFERENCES Customers (CustomerID)
);
Soit on peut lister toutes les contraintes reliées à la table qui nous intéresse :
SELECT
tc.Constraint_Name,
tc.Constraint_Type,
kcu.Column_Name,
tc.Table_Name,
tc.is_deferrable,
tc.initially_deferred
FROM
INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc
JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE kcu
ON tc.Constraint_Name = kcu.Constraint_Name
WHERE
tc.Table_Name = 'VotreNomDeTable'
AND tc.CONSTRAINT_SCHEMA = 'VotreSchema'
ORDER BY
tc.Constraint_Name, kcu.Ordinal_Position;
Une fois la contrainte identifiée, ici FK_Customer_Order, on remplace les scripts pré et post SQL par les suivants :
-- Script Pré-SQL
array('ALTER TABLE [schema].[table_enfant] NOCHECK CONSTRAINT FK_Customer_Order;')
-- Script Post-SQL
array('ALTER TABLE [schema].[table_enfant] WITH CHECK CHECK CONSTRAINT FK_Customer_Order;')
