J’évoquais ici un script de suppression / régénération de clés primaires. Mais une clé primaire ne peut pas être supprimée tant que des clés étrangères s’appuient sur elle. Voici donc un script permettant de générer le nécessaire pour supprimer puis reconstruire les clés étrangères sur une base de données.

Lorsque l’on cherche à supprimer une clé primaire qui est encore référencée par une clé étrangère, on obtient une erreur du type :

Voici donc les outils nécessaires pour temporairement supprimer des clés étrangères, par exemple le temps de retoucher quelques paramètres sur des clés primaires, et surtout pour remettre en place ces clés étrangères après coup.

Dans un premier temps, voici de quoi supprimer toutes les clés étrangères d’une base :

-- Générateur de script de suppressions de clés étrangères
-- Auteur : Jean-Nicolas BERGER (www.sqlserver.fr)

SELECT 'ALTER TABLE ' + QUOTENAME(s.name) + '.' + QUOTENAME(t.name) + ' DROP CONSTRAINT ' + QUOTENAME(fk.name)+ ';'
FROM sys.foreign_keys fk
join sys.tables t on fk.parent_object_id=t.object_id
join sys.schemas s on s.schema_id=t.schema_id

Et bien sûr, avant de lancer les commandes de suppression, préparez un script pour les reconstruire après la bataille :

-- Générateur de script de création de clés étrangères
-- Auteur : Jean-Nicolas BERGER (www.sqlserver.fr)

SET NOCOUNT ON

declare @Liste table (fk_id int,
						DebutCreate varchar(max),
						MilieuCreate varchar(max),
						FinCreate varchar(max),
						ListeColonnesParent varchar(max),
						ListeColonnesRef varchar(max))

declare @fk_id int
declare @ListeColonnesParent varchar(max), @ListeColonnesRef varchar(max)

INSERT INTO @Liste(fk_id,DebutCreate,MilieuCreate,FinCreate)
SELECT fk.object_id,
	'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 (',
	') REFERENCES ' + QUOTENAME(s_r.name) + '.' + QUOTENAME(t_r.name) + ' (',
	') ON DELETE ' + REPLACE(fk.delete_referential_action_desc,'_',' ') + ' ON UPDATE ' + REPLACE(fk.update_referential_action_desc,'_',' ')
		+ case WHEN fk.is_not_for_replication=1 then ' NOT FOR REPLICATION' ELSE '' end
		+ ';' + case when fk.is_disabled=1 then 'ALTER TABLE ' + QUOTENAME(s.name) + '.' + QUOTENAME(t.name) + ' NOCHECK CONSTRAINT ' + QUOTENAME(fk.name) + ';' else '' end
FROM sys.foreign_keys fk
join sys.tables t on fk.parent_object_id=t.object_id
join sys.schemas s on s.schema_id=t.schema_id
join sys.tables t_r on fk.referenced_object_id=t_r.object_id
join sys.schemas s_r on s_r.schema_id=t_r.schema_id

-- Pour chaque FK
DECLARE curseur CURSOR FOR 
SELECT fk_id
FROM @Liste

OPEN curseur
FETCH NEXT FROM curseur INTO @fk_id

WHILE @@FETCH_STATUS = 0
BEGIN
	-- Construction de la liste des colonnes de la table parente
	SELECT @ListeColonnesParent=','
	SELECT @ListeColonnesParent+=quotename(c.name)+','
	FROM sys.foreign_key_columns fkc
		join sys.columns c on c.object_id=fkc.parent_object_id
							and c.column_id=fkc.parent_column_id
	where fkc.constraint_object_id=@fk_id
	order by fkc.constraint_column_id

	-- Construction de la liste des colonnes de la table référencée
	SELECT @ListeColonnesRef=','
	SELECT @ListeColonnesRef+=quotename(c.name)+','
	FROM sys.foreign_key_columns fkc
		join sys.columns c on c.object_id=fkc.referenced_object_id
							and c.column_id=fkc.referenced_column_id
	where fkc.constraint_object_id=@fk_id
	order by fkc.constraint_column_id

	-- Mise en forme des listes de colonnes
	SELECT @ListeColonnesParent=SUBSTRING(@ListeColonnesParent,2,len(@ListeColonnesParent)-2)
	SELECT @ListeColonnesRef=SUBSTRING(@ListeColonnesRef,2,len(@ListeColonnesRef)-2)

	update @Liste
	set ListeColonnesParent=@ListeColonnesParent,
		ListeColonnesRef=@ListeColonnesRef
	where fk_id=@fk_id

	FETCH NEXT FROM curseur INTO @fk_id
END

CLOSE curseur
DEALLOCATE curseur

SELECT DebutCreate+ListeColonnesParent+MilieuCreate+ListeColonnesRef+FinCreate
FROM @Liste

Comme d’habitude, n’hésitez pas à me dire si ce script vous a été utile et à le commenter si vous pensez qu’il peut être amélioré…