juin
2010
Un article de zinzineti
Pour des raisons X ou Y on peut être amener à modifier la colonne auto-incrément d’une table. Comment donc changer par exemple la colonne ID(1,1) pour avoir ID(2,2) ?. On peut penser qu’un ALTER TABLE suffit à faire la modification. Mais Non…
/***********************************************************
— Description : Modifier la colonne IDENTITY d’une table.
— Auteur : Etienne ZINZINDOHOUE
***********************************************************/
–Soit la table initiale avec une colonne autoincrément(1,1)
[ID_USER] [bigint] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[LOGIN] [varchar](20) NULL,
[PWD] [varchar](15) NULL,
[NOM] [varchar](20) NULL,
[PRENOM] [varchar](20) NULL
-- Clé primaire (PK)
CONSTRAINT [PK_UTILISATEUR] PRIMARY KEY CLUSTERED
(
[ID_USER] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
-- Clé étrangère (FK) -----
ALTER TABLE [dbo].[UTILISATEUR] WITH CHECK ADD CONSTRAINT [FK_USER_IDUSER] FOREIGN KEY([ID_USER])
REFERENCES [dbo].[GROUPE] ([ID_USER])
GO
ALTER TABLE [dbo].[UTILISATEUR] CHECK CONSTRAINT [FK_USER_IDUSER]
GO
Pour des raisons X ou Y on peut être amener à modifier la
colonne
auto-incrément ID_USER (1,1) pour le mettre à ID_USER (2,2) par exemple.
On pourrait penser qu’un ALTER TABLE suffit à faire la modification. Mais Non…
Vous ne pouvez pas faire la modification avec la comande :
ALTER COLUMN [ID_USER] [bigint] IDENTITY(2,2) NOT FOR REPLICATION NOT NULL
Vous aurez dans ce cas une erreur.
Alors qu’il bien possible de modifier par exemple la colonne LOGIN avec la même commande :
ALTER COLUMN [LOGIN] [varchar](21)
Alors comment modifier la colonne IDENTITY d’une table ?
Voici la solution :
–> 1.) Créer une table temporaire ayant la même structure que la table à modifier
–> 2.) Importer des données de la table initiale vers la table temporaire
–> 3.) Supprimer les clées primaires (PK) et étrangères (FK) de la table initiale
–> 4.) Renommer la table temporaire (nom de la table initiale à modifier)
–> 5.) Ajouter les clés primaires (PK) et étrangères (FK)
BEGIN TRANSACTION
USE MA_BD
-- Creer une table temporaire ayant les mêmes définitions que la table initiale qu'on désire modifier excepté les clés primaires et étrangères (PK,FK,...)
CREATE TABLE Tmp_UTILISATEUR
(
[ID_USER] BIGINT IDENTITY(2,2) NOT FOR REPLICATION NOT NULL,
[LOGIN] VARCHAR(20) NULL,
[PWD] VARCHAR(15) NULL,
[NOM] VARCHAR(20) NULL,
[PRENOM] VARCHAR(20) NULL
) ON [PRIMARY]
-- Autoriser l'insertion de valeurs explicites dans la colonne IDENTITY(auto incrément)
SET IDENTITY_INSERT Tmp_UTILISATEUR ON
-- Copier les données de la table initiale vers la table temporaire
IF EXISTS(SELECT * FROM [UTILISATEUR])
EXEC('INSERT INTO dbo.Tmp_UTILISATEUR(ID_USER,LOGIN,PWD,NOM,PRENOM)
SELECT ID_USER,LOGIN,PWD,NOM,PRENOM
FROM dbo.[UTILISATEUR] WITH (HOLDLOCK TABLOCKX)')
-- Désactiver l'insertion de valeurs explicites dans la colonne IDENTITY(auto incrément)
SET IDENTITY_INSERT Tmp_USER OFF
-- Supprimer la clé étrangères (FK) de la table [UTILISATEUR]
ALTER TABLE [dbo].[USER]
DROP CONSTRAINT [FK_USER_IDUSER]
-- Supprimer la table initiale
DROP TABLE [UTILISATEUR]
-- Renommer la table temporaire de façon à prendre maintenant le nom de la table initiale
EXECUTE sp_rename N'Tmp_UTILISATEUR', N'UTILISATEUR', 'OBJECT'
-- Ajouter la contrainte de clé primaire (PK)
ALTER TABLE [UTILISATEUR] ADD CONSTRAINT
PK_USER PRIMARY KEY CLUSTERED
(
ID_USER
) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
-- Ajouter la contrainte de clé étrangère (FK)
ALTER TABLE [dbo].[UTILISATEUR] WITH CHECK ADD CONSTRAINT [FK_USER_IDUSER] FOREIGN KEY([ID_USER])
REFERENCES [dbo].[GROUPE] ([ID_USER])
-- Fin de la transaction
COMMIT TRANSACTION
/***********************************************************
— Description : Modifier la colonne IDENTITY d’une table.
— Auteur : Etienne ZINZINDOHOUE
***********************************************************/