Discussion :

[framus.class]

Bonjour,
J'ai une table dont la clé primaire est un varchar(4) (dû à des raisons historiques) elle est référencée par plusieurs autres table, pour des raisons fonctionnelles je désire migrer la clé de cette table vers int identity . est-il possible de le faire ? et surtout comment le faire? pour faire simple imaginons une table Personne qui est référencé par 3 autres table: Compte , AdresseLivraison, AdresseFacturation. donc la question est de savoir comment modifier le type et la stratégie de génération de la clé de la table Personne de telle sorte que ces changements soient impactés sur les 3 tables qui la référencent.
Merci beaucoup pour votre aide!

[elsuket]

Bonjour,

Très bonne initiative.
Pour cela il vous faut :

- ajouter une colonne de type entier avec la propriété d'auto-incrémentation à la table Personne
- ajouter une colonne aux tables qui référencent Personne
- réaliser la mise à jour de cette colonne dans les tables référençantes
- supprimer les contraintes de clé étrangère originelles
- ajouter une contrainte de clé primaire à la table Personne
- ajouter une contrainte de clé étrangère référençant cette colonne dans les tables référençantes.
- supprimer l'ancienne colonne de clé primaire de la table Personne

Et voici les détails :

- ajouter une colonne de type entier avec la propriété d'auto-incrémentation à la table Personne

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

ALTER TABLE dbo.Personne ADD personne_id INT IDENTITY

Notez que si votre table contient ou qu'il est prévu qu'elle contienne plus de 2 milliards de lignes ((2 147 483 647 pour être exact) mais moins de 4 milliards, vous pouvez remplacer par IDENTITY(-2 147 483 648, 1) ou utiliser le type de données BIGINT à la place de INT pour d'autres tables ou vous auriez le même besoin.

- ajouter une colonne aux tables qui référencent Personne

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
ALTER TABLE dbo.Compte
ADD personne_id INT
GO
 
ALTER TABLE  dbo.AdresseLivraison
ADD personne_id INT
GO
 
ALTER TABLE dbo.AdresseFacturation
ADD personne_id INT
GO

- réaliser la mise à jour de cette colonne dans les tables référençantes

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
UPDATE dbo.Compte
SET personne_id = P.personne_id
FROM dbo.Compte AS C
INNER JOIN dbo.Personne AS T ON C.maCleVarcharPersonne= T.maCleVarcharPersonne
GO
 
UPDATE dbo.AdresseLivraison
SET personne_id = T.personne_id
FROM dbo.AdresseLivraison AS AL
INNER JOIN dbo.Personne AS T ON AL.maCleVarcharPersonne= T.maCleVarcharPersonne
GO
 
UPDATE dbo.AdresseFacturation
SET personne_id = T.personne_id
FROM dbo.AdresseFacturationAS AF
INNER JOIN dbo.Personne AS T ON AF.maCleVarcharPersonne= T.maCleVarcharPersonne
GO

Une fois cela fait vous pouvez ajouter une contrainte de valuation obligatoire de la colonne dans les tables référençantes :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
ALTER TABLE dbo.Compte ALTER COLUMN personne_id INT NOT NULL
GO
ALTER TABLE dbo.AdresseLivraison ALTER COLUMN personne_id INT NOT NULL
GO
ALTER TABLE dbo.AdresseFacturation ALTER COLUMN personne_id INT NOT NULL
GO

- supprimer les contraintes de clé étrangère originelles

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
ALTER TABLE dbo.Compte DROP CONSTRAINT Compte_maVielleContrainteReferençant_maCleVarcharPersonne
GO
 
ALTER TABLE dbo.AdresseLivraison DROP CONSTRAINT AdresseLivraison_maVielleContrainteReferençant_maCleVarcharPersonne
GO
 
ALTER TABLE dbo.AdresseFacturation DROP CONSTRAINT AdresseFacturation_maVielleContrainteReferençant_maCleVarcharPersonne
GO

- ajouter une contrainte de clé primaire à la table Personne

Pour cela vous êtes obligé de supprimer l'ancienne :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
ALTER TABLE dbo.Personne DROP CONSTRAINT maVielleContrainteDeClePrimaireVarchar
GO

Et voici comment recréer la nouvelle :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
ALTER TABLE dbo.Personne ADD CONSTRAINT maNouvelleContrainteDeClePrimaire PRIMARY KEY (personne_id)
GO

Notez que cela crée implicitement un index cluster sur la table.
Vous pouvez lever une exception à la suppression de la clé primaire parce que les index non-cluster de la table dépendent de l'index cluster.
Dans ce cas supprimez tous les index, sauf l'index cluster (il est marqué en tant que tel dans l'explorateur d'objets de SQL Server Management Studio, F7)

- ajouter une contrainte de clé étrangère référençant cette colonne dans les tables référençantes.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
ALTER TABLE  dbo.Compte ADD CONSTRAINT Compte_maNouvelleContrainteDeCleEtrangère FOREIGN KEY (personne_id) REFERENCES dbo.Personne
GO
 
ALTER TABLE  dbo.AdresseLivraison ADD CONSTRAINT AdresseLivraison_maNouvelleContrainteDeCleEtrangère FOREIGN KEY (personne_id) REFERENCES dbo.Personne
GO
 
ALTER TABLE  dbo.AdresseFacturation ADD CONSTRAINT AdresseFacturation_maNouvelleContrainteDeCleEtrangère FOREIGN KEY (personne_id) REFERENCES dbo.Personne
GO

- supprimer l'ancienne colonne de clé primaire de la table Personne

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
ALTER TABLE dbo.Personne
DROP COLUMN maVielleColonne_ContrainteDeClePrimaireVarchar

Normalement je n'ai rien oublié, et vous êtes rendu

@++

[Invité]

J'aurais procédé de même !

C'est un gros boulot... D'où l'intérêt de bien prévoir ces clefs en amont !!!

ps : le néologisme "référençantes" pourrait être avantageusement remplacé par le mot référentes.

[framus.class]

Merci elsuket , on peut pas avoir une réponse meilleurs que la tienne ! je te tire mon chapeau

[elsuket]

C'est un gros boulot... D'où l'intérêt de bien prévoir ces clefs en amont !!!

ps : le néologisme "référençantes" pourrait être avantageusement remplacé par le mot référentes.

Il est clair que le choix des clefs est très important.
Malheureusement même dans les universités, c'est loin de ce qu'on vous en apprend, l'aspect des performances des bases de données n'est pas abordé ...
Probablement parce que la plupart des élèves deviennent développeurs d'application et pas de base de données

D'accord avec vous pour le néologisme

@framus.class : avec plaisir

@++