{"id":623,"date":"2012-07-23T00:00:11","date_gmt":"2012-07-22T22:00:11","guid":{"rendered":"http:\/\/www.sqlserver.fr\/blog\/?p=623"},"modified":"2026-05-02T14:31:07","modified_gmt":"2026-05-02T12:31:07","slug":"regenerer-les-cles-etrangeres","status":"publish","type":"post","link":"https:\/\/www.sqlserver.fr\/blog\/regenerer-les-cles-etrangeres\/","title":{"rendered":"R\u00e9g\u00e9n\u00e9rer les cl\u00e9s \u00e9trang\u00e8res"},"content":{"rendered":"

J’\u00e9voquais ici<\/a> un script de suppression \/ r\u00e9g\u00e9n\u00e9ration de cl\u00e9s primaires. Mais une cl\u00e9 primaire ne peut pas \u00eatre supprim\u00e9e tant\u00a0que des cl\u00e9s \u00e9trang\u00e8res s’appuient sur elle. Voici donc un script permettant de g\u00e9n\u00e9rer le n\u00e9cessaire pour supprimer puis reconstruire les cl\u00e9s \u00e9trang\u00e8res sur une base de donn\u00e9es.<\/p>\n

Lorsque l’on cherche \u00e0 supprimer une cl\u00e9 primaire qui est encore r\u00e9f\u00e9renc\u00e9e par une cl\u00e9 \u00e9trang\u00e8re, on obtient une erreur du type :<\/p>\n

\"\"<\/a><\/p>\n

Voici donc les outils n\u00e9cessaires pour temporairement supprimer des cl\u00e9s \u00e9trang\u00e8res, par exemple le temps de retoucher quelques param\u00e8tres sur des cl\u00e9s primaires, et surtout pour remettre en place ces cl\u00e9s \u00e9trang\u00e8res apr\u00e8s coup.<\/p>\n

Dans un premier temps, voici de quoi supprimer toutes les cl\u00e9s \u00e9trang\u00e8res d’une base :<\/p>\n

-- G\u00e9n\u00e9rateur de script de suppressions de cl\u00e9s \u00e9trang\u00e8res\r\n-- Auteur : Jean-Nicolas BERGER (www.sqlserver.fr)\r\n\r\nSELECT 'ALTER TABLE ' + QUOTENAME(s.name) + '.' + QUOTENAME(t.name) + ' DROP CONSTRAINT ' + QUOTENAME(fk.name)+ ';'\r\nFROM sys.foreign_keys fk\r\njoin sys.tables t on fk.parent_object_id=t.object_id\r\njoin sys.schemas s on s.schema_id=t.schema_id<\/pre>\n
Et bien s\u00fbr, avant de lancer les commandes de suppression, pr\u00e9parez un script pour les reconstruire apr\u00e8s la bataille :<\/p>\n
-- G\u00e9n\u00e9rateur de script de cr\u00e9ation de cl\u00e9s \u00e9trang\u00e8res\r\n-- Auteur : Jean-Nicolas BERGER (www.sqlserver.fr)\r\n\r\nSET NOCOUNT ON\r\n\r\ndeclare @Liste table (fk_id int,\r\n\t\t\t\t\t\tDebutCreate varchar(max),\r\n\t\t\t\t\t\tMilieuCreate varchar(max),\r\n\t\t\t\t\t\tFinCreate varchar(max),\r\n\t\t\t\t\t\tListeColonnesParent varchar(max),\r\n\t\t\t\t\t\tListeColonnesRef varchar(max))\r\n\r\ndeclare @fk_id int\r\ndeclare @ListeColonnesParent varchar(max), @ListeColonnesRef varchar(max)\r\n\r\nINSERT INTO @Liste(fk_id,DebutCreate,MilieuCreate,FinCreate)\r\nSELECT fk.object_id,\r\n\t'ALTER TABLE ' + QUOTENAME(s.name) + '.' + QUOTENAME(t.name) + ' WITH ' + case when fk.is_not_trusted=1 then 'NOCHECK' else 'CHECK' end + ' ADD CONSTRAINT ' + QUOTENAME(fk.name) + ' FOREIGN KEY (',\r\n\t') REFERENCES ' + QUOTENAME(s_r.name) + '.' + QUOTENAME(t_r.name) + ' (',\r\n\t') ON DELETE ' + REPLACE(fk.delete_referential_action_desc,'_',' ') + ' ON UPDATE ' + REPLACE(fk.update_referential_action_desc,'_',' ')\r\n\t\t+ case WHEN fk.is_not_for_replication=1 then ' NOT FOR REPLICATION' ELSE '' end\r\n\t\t+ ';' + case when fk.is_disabled=1 then 'ALTER TABLE ' + QUOTENAME(s.name) + '.' + QUOTENAME(t.name) + ' NOCHECK CONSTRAINT ' + QUOTENAME(fk.name) + ';' else '' end\r\nFROM sys.foreign_keys fk\r\njoin sys.tables t on fk.parent_object_id=t.object_id\r\njoin sys.schemas s on s.schema_id=t.schema_id\r\njoin sys.tables t_r on fk.referenced_object_id=t_r.object_id\r\njoin sys.schemas s_r on s_r.schema_id=t_r.schema_id\r\n\r\n-- Pour chaque FK\r\nDECLARE curseur CURSOR FOR \r\nSELECT fk_id\r\nFROM @Liste\r\n\r\nOPEN curseur\r\nFETCH NEXT FROM curseur INTO @fk_id\r\n\r\nWHILE @@FETCH_STATUS = 0\r\nBEGIN\r\n\t-- Construction de la liste des colonnes de la table parente\r\n\tSELECT @ListeColonnesParent=','\r\n\tSELECT @ListeColonnesParent+=quotename(c.name)+','\r\n\tFROM sys.foreign_key_columns fkc\r\n\t\tjoin sys.columns c on c.object_id=fkc.parent_object_id\r\n\t\t\t\t\t\t\tand c.column_id=fkc.parent_column_id\r\n\twhere fkc.constraint_object_id=@fk_id\r\n\torder by fkc.constraint_column_id\r\n\r\n\t-- Construction de la liste des colonnes de la table r\u00e9f\u00e9renc\u00e9e\r\n\tSELECT @ListeColonnesRef=','\r\n\tSELECT @ListeColonnesRef+=quotename(c.name)+','\r\n\tFROM sys.foreign_key_columns fkc\r\n\t\tjoin sys.columns c on c.object_id=fkc.referenced_object_id\r\n\t\t\t\t\t\t\tand c.column_id=fkc.referenced_column_id\r\n\twhere fkc.constraint_object_id=@fk_id\r\n\torder by fkc.constraint_column_id\r\n\r\n\t-- Mise en forme des listes de colonnes\r\n\tSELECT @ListeColonnesParent=SUBSTRING(@ListeColonnesParent,2,len(@ListeColonnesParent)-2)\r\n\tSELECT @ListeColonnesRef=SUBSTRING(@ListeColonnesRef,2,len(@ListeColonnesRef)-2)\r\n\r\n\tupdate @Liste\r\n\tset ListeColonnesParent=@ListeColonnesParent,\r\n\t\tListeColonnesRef=@ListeColonnesRef\r\n\twhere fk_id=@fk_id\r\n\r\n\tFETCH NEXT FROM curseur INTO @fk_id\r\nEND\r\n\r\nCLOSE curseur\r\nDEALLOCATE curseur\r\n\r\nSELECT DebutCreate+ListeColonnesParent+MilieuCreate+ListeColonnesRef+FinCreate\r\nFROM @Liste<\/pre>\n
Comme d’habitude, n’h\u00e9sitez pas \u00e0 me dire si ce script vous a \u00e9t\u00e9 utile et \u00e0 le commenter si vous pensez qu’il peut \u00eatre am\u00e9lior\u00e9…<\/p>\n","protected":false},"excerpt":{"rendered":"
J’\u00e9voquais ici un script de suppression \/ r\u00e9g\u00e9n\u00e9ration de cl\u00e9s primaires. Mais une cl\u00e9 primaire ne peut pas \u00eatre supprim\u00e9e tant\u00a0que des cl\u00e9s \u00e9trang\u00e8res s’appuient sur elle. Voici donc un script permettant de g\u00e9n\u00e9rer le n\u00e9cessaire pour supprimer puis reconstruire … Continuer la lecture →<\/span><\/a><\/p>\n","protected":false},"author":7,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[5],"tags":[],"class_list":["post-623","post","type-post","status-publish","format-standard","hentry","category-outils"],"_links":{"self":[{"href":"https:\/\/www.sqlserver.fr\/blog\/wp-json\/wp\/v2\/posts\/623","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sqlserver.fr\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sqlserver.fr\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sqlserver.fr\/blog\/wp-json\/wp\/v2\/users\/7"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sqlserver.fr\/blog\/wp-json\/wp\/v2\/comments?post=623"}],"version-history":[{"count":12,"href":"https:\/\/www.sqlserver.fr\/blog\/wp-json\/wp\/v2\/posts\/623\/revisions"}],"predecessor-version":[{"id":1946,"href":"https:\/\/www.sqlserver.fr\/blog\/wp-json\/wp\/v2\/posts\/623\/revisions\/1946"}],"wp:attachment":[{"href":"https:\/\/www.sqlserver.fr\/blog\/wp-json\/wp\/v2\/media?parent=623"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlserver.fr\/blog\/wp-json\/wp\/v2\/categories?post=623"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlserver.fr\/blog\/wp-json\/wp\/v2\/tags?post=623"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}