Les clés étrangères jouent un rôle crucial dans le maintien de l’intégrité référentielle entre les tables d’une base de données SQL Server. Elles garantissent que les enregistrements parent ne peuvent pas être supprimés s’il existe des enregistrements enfants. Si vous n’utilisez pas actuellement de clés étrangères dans votre base de données, il est fortement recommandé de commencer à les implémenter.
Cependant, bien que les clés étrangères soient utiles pour maintenir l’intégrité des données, elles peuvent également introduire une lenteur lors de la suppression de données, en particulier lors de la suppression de l’enregistrement parent. SQL Server doit vérifier toutes les clés étrangères pour s’assurer qu’il n’y a pas d’enregistrements enfants associés à l’enregistrement parent. Ce processus peut devenir fastidieux, surtout lorsqu’il s’agit d’un grand nombre de clés étrangères.
Récemment, j’ai dû supprimer certains enregistrements parent d’une table qui comportait plus de 30 clés étrangères. Bien que SQL Server puisse facilement identifier les violations de clés étrangères lors de la suppression d’un enregistrement parent, il peut être difficile de trouver tous les enregistrements enfants associés à chaque clé étrangère.
Heureusement, SQL Server nous fournit des tables système qui contiennent des informations précieuses sur les clés étrangères, y compris les tables parent et enfant, ainsi que les colonnes utilisées dans la relation de clé étrangère. En exploitant ces informations, nous pouvons générer dynamiquement une instruction SELECT qui révèle le nombre d’enregistrements enfants liés à chaque ID parent.
Les tables système suivantes peuvent être utilisées pour générer l’instruction SELECT :
- Sys.foreign_key_columns : Cette table fournit des informations sur les colonnes utilisées dans la définition de la clé étrangère, ainsi que sur les tables parent et enfant.
- Sys.columns : En joignant cette table à la précédente, nous pouvons obtenir les noms de colonnes utilisés dans la relation de clé étrangère.
- Sys.objects : Cette table nous aide à prendre en compte les différents schémas utilisés dans la base de données, ce qui nous permet de déterminer le nom du schéma des tables parent et enfant.
Avec cette base, nous pouvons construire une instruction SELECT qui fournit les informations suivantes :
- Nom de la table parent
- Nom de la colonne utilisée dans la table parent
- Nom de la table enfant
- Nom de la colonne utilisée dans la table enfant
Voici un exemple de l’instruction SELECT :
-- Table parente
DECLARE @tableName VARCHAR(150) = 'dbo.Product'
SELECT
OBJECT_NAME(fkc.referenced_object_id) AS 'Table parente',
parentcolumns.name AS 'Colonne parente',
OBJECT_NAME(fkc.parent_object_id) AS 'Table enfant',
childcolumns.name AS 'Colonne enfant',
'SELECT COUNT(1) as ''RowCount'', ''' + OBJECT_NAME(fkc.parent_object_id)+ ''' as ''TableEnfant'' from ' + quotename(schema_name(o1.schema_id)) + '.' + object_name(fkc.referenced_object_id) + ' x
INNER JOIN ' + quotename(schema_name(o2.schema_id)) + '.' + QUOTENAME(OBJECT_NAME(fkc.parent_object_id)) + ' y ON x.' + parentcolumns.name + ' = y.'+ childcolumns.name + ' UNION'
FROM sys.foreign_key_columns fkc
INNER JOIN sys.columns childcolumns ON fkc.parent_object_id = childcolumns.object_id AND fkc.parent_column_id = childcolumns.column_id -- obtenir les colonnes enfants
INNER JOIN sys.columns parentcolumns ON fkc.referenced_object_id = parentcolumns.object_id AND fkc.referenced_column_id = parentcolumns.column_id -- obtenir les colonnes parent
INNER JOIN sys.objects o1 ON fkc.referenced_object_id = o1.object_id -- obtenir le nom du schéma parent
INNER JOIN sys.objects o2 ON fkc.parent_object_id = o2.object_id -- obtenir le nom du schéma enfant
WHERE fkc.referenced_object_id = OBJECT_ID(@tableName)
Si vous souhaitez voir toutes les tables de la base de données, vous pouvez omettre la clause WHERE dans la requête ci-dessus.
En exécutant l’instruction SELECT, vous obtiendrez un ensemble de résultats qui affiche toutes les tables enfants et le nombre correspondant d’enregistrements enfants liés à chaque clé étrangère parente. Ces informations peuvent être précieuses pour déterminer quelles lignes enfants doivent être supprimées avant de supprimer les enregistrements parent.
De plus, vous pouvez modifier la requête pour trouver des enregistrements pour un ensemble de données spécifique. Par exemple, si vous avez un sous-ensemble d’identifiants uniques basés sur la définition de la clé étrangère, vous pouvez ajuster la requête pour trouver tous les enregistrements enfants associés à ces valeurs.
Il est important de noter que les exemples ci-dessus utilisent une variable de table à des fins de démonstration. Cependant, vous pouvez facilement la remplacer par une table physique ou une table temporaire. N’oubliez pas de créer la table dans la session où vous exécutez les plus grandes instructions SELECT.
En conclusion, SQL Server nous fournit des outils puissants pour comprendre et gérer les relations de clés étrangères. En exploitant les tables système et les requêtes dynamiques, nous pouvons facilement identifier les enregistrements enfants associés aux clés étrangères parentes, ce qui nous permet de maintenir l’intégrité des données et de prendre des décisions éclairées lors de la suppression des enregistrements parent.
N’oubliez pas de faire preuve de prudence lors de l’exécution de tout code obtenu sur Internet et de le tester toujours dans un environnement contrôlé.
Merci de votre lecture !
© 2021 Votre Nom. Tous droits réservés.