décembre
2010
Un article de zinzineti
L’intégrité référentielle ou contrainte de clé étrangère (CONSTRAINT FOREIGN KEY) permet de contrôler la validité, la cohérence et la consistance des données dans une base. Ce mécanisme assure donc une meilleure qualité des données. Elle met également en exergue les liens entre les tables et permet ainsi d’avoir une bonne visibilité de la structure de la base de données. À travers des exemples simples [mais pas choisit au hazard ;-)], nous allons examiner l’impact de l’intégrité référentielle sur la performance des requêtes. Cette analyse va nous conduire à mettre en évidence les situations où on peut être amené à désactiver/activer la contrainte de clé étrangère afin d’optimiser la performance des requêtes.
—————————————————————————————
–> Contrainte FOREIGN KEY (FK) et visibilité de la structure de la base de données
—————————————————————————————
La contrainte FK permet de mieux visualiser les liens entre les tables.
Exemple : Considérons 2 paires de tables :
• T_PARENT et T_FILLE avec FK
• T_PARENT_BIS et T_FILLE_BIS sans FK
DROP TABLE dbo.T_FILLE
DROP TABLE dbo.T_FILLE_BIS
DROP TABLE dbo.T_PARENT
DROP TABLE dbo.T_PARENT_BIS
CREATE TABLE dbo.T_PARENT
(
idp INT NOT NULL PRIMARY KEY,
valp varchar(20) NOT NULL
)
CREATE TABLE dbo.T_FILLE
(
idf INT NOT NULL PRIMARY KEY,
idp INT NOT NULL CONSTRAINT FK_idp FOREIGN KEY REFERENCES T_PARENT(idp),
valf VARCHAR(20) NOT NULL
)
CREATE TABLE dbo.T_PARENT_BIS
(
idp INT NOT NULL PRIMARY KEY,
valp varchar(20) NOT NULL
)
CREATE TABLE dbo.T_FILLE_BIS
(
idf INT NOT NULL PRIMARY KEY,
idp INT NOT NULL,
valf VARCHAR(20) NOT NULL
)
–Vérifions la présence de clé étrangère
EXEC sp_fkeys 'T_PARENT'
Résultat : Présence de FK pour la table T_PARENT
——–
EXEC sp_fkeys 'T_PARENT_BIS'
Résultat : Pas de FK pour la table T_PARENT_BIS
——–
En créant un diagramme pour chaque paire de table, on constate que la présence de FK
rend plus visible la liaison entre les tables.
T_PARENT et T_FILLE
———————
 |
T_PARENT_BIS et T_FILLE_BIS
—————————–
 |
—————————————————————————————
–> Contrainte FOREIGN KEY (FK) et requêtes
—————————————————————————————
Créons et alimentons les deux paires de tables
–Cration des tables
DROP TABLE dbo.T_FILLE
DROP TABLE dbo.T_FILLE_BIS
DROP TABLE dbo.T_PARENT
DROP TABLE dbo.T_PARENT_BIS
CREATE TABLE dbo.T_PARENT
(
idp INT NOT NULL PRIMARY KEY,
valp varchar(20) NOT NULL
)
David,
Je suis parfaitement d’accord avec ta remarque.
Dans mon cas l’activation de la FK suivi de la validation des valeurs insérer/modifier devrait s’écrire :
ALTER TABLE T_FILLE WITH CHECK CHECK CONSTRAINT FK_idp
Merci pour ta remarque.
Dans ton article, il y a un lien qui pointe vers un billet de Elsuket, qui montre comment
]
d’obtenir les contraintes qui ne sont pas fiables. Je trouve ça interessant et je mets le lien ici pour retrouver facilement quand je serai dans le besoin [Merci Elsuket
http://blog.developpez.com/elsuket/p8759/moteur-de-base-de-donnees-sql-server/trouver-les-contraintes-qui-ne-sont-pas-/
A+
A noter également que la réactivation des clés étrangères doit passer par l’option WITH CHECK pour pouvoir être prise en compte par l’optimiseur dans certains cas :
http://blog.developpez.com/mikedavem/p8780/sql-server-2005/sql/contraintes-sql-et-influence-sur-les-per/
A+