Identity field issue

[j252ewv]

I am trying to convert the ID field on the table termdates to be an auto increment id field

can one of you clver sorts (Sql sister probably) tell me where/why this is going wrong?


alter table Termdates alter column [ID] Identity(1,1) not null

 

[gmmastros]

You cannot change a column that was NOT an identity column to an identity column. Can't be done.

Specifically, if your column was already an identity column, you can change the seed and increment values. But you cannot take a column that was NOT an identity column and make it become an identity column.

There are, of course, ways to get around this.

You could create a new column in the table as an identity column. Then set Identity_insert on and populate this column with values in the ID column. Then, drop the id column and rename the new column to ID.

There are, of course, problems with doing this, but I think this is the only way. The problem with this method is.... ID could be a primary key, so you would need to drop the primary key constraint before dropping the column, then re-create the primary key after renaming the new column.

Also, if ID participates in a foreign key relationship, you will need to drop that and re-create later.

Also, if ID is indexed (separately from the primary key and foreign key) then you will need to re-create the indexes as well.

Make sense?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[j252ewv]

the coumn is an identity field which has been turned off for data import.
I am lloking for a script which will turn it back on.

 

[TysonLPrice]

This isn't exactly what you are asking but it appears, to laymans eyes, you can add an identity to an existing column through Enterprise Manager. This is the code it generates. This is the link I saw this at. It does back up what gmmastros posted.

http://www.sqlmag.com/articles/index.cfm?articleid=22080

/* To prevent any potential data loss issues, you should review this script in detail before running it outside the context of the database designer.*/
BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
GO
ALTER TABLE TPrice.OrderDetails2
	DROP CONSTRAINT FK_OrderDetails2_Products
GO
COMMIT
BEGIN TRANSACTION
GO
ALTER TABLE TPrice.OrderDetails2
	DROP CONSTRAINT FK_OrderDetails2_Orders2
GO
COMMIT
BEGIN TRANSACTION
GO
CREATE TABLE TPrice.Tmp_OrderDetails2
	(
	OrderID int NOT NULL IDENTITY (1, 1),
	ProductID int NOT NULL,
	Quantity smallint NOT NULL
	)  ON [PRIMARY]
GO
SET IDENTITY_INSERT TPrice.Tmp_OrderDetails2 ON
GO
IF EXISTS(SELECT * FROM TPrice.OrderDetails2)
	 EXEC('INSERT INTO TPrice.Tmp_OrderDetails2 (OrderID, ProductID, Quantity)
		SELECT OrderID, ProductID, Quantity FROM TPrice.OrderDetails2 WITH (HOLDLOCK TABLOCKX)')
GO
SET IDENTITY_INSERT TPrice.Tmp_OrderDetails2 OFF
GO
DROP TABLE TPrice.OrderDetails2
GO
EXECUTE sp_rename N'TPrice.Tmp_OrderDetails2', N'OrderDetails2', 'OBJECT' 
GO
ALTER TABLE TPrice.OrderDetails2 ADD CONSTRAINT
	PK_OrderDetails2 PRIMARY KEY CLUSTERED 
	(
	OrderID,
	ProductID
	) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

GO
ALTER TABLE TPrice.OrderDetails2 ADD CONSTRAINT
	FK_OrderDetails2_Orders2 FOREIGN KEY
	(
	OrderID
	) REFERENCES TPrice.Orders2
	(
	OrderID
	) ON UPDATE  NO ACTION 
	 ON DELETE  NO ACTION 
	
GO
ALTER TABLE TPrice.OrderDetails2 ADD CONSTRAINT
	FK_OrderDetails2_Products FOREIGN KEY
	(
	ProductID
	) REFERENCES dbo.Products
	(
	ProductID
	) ON UPDATE  NO ACTION 
	 ON DELETE  NO ACTION 
	
GO
COMMIT

 

[SQLSister]

Warning, do NOT try to generate an identity through Enterprise Manager if you have a large table. This is a time-consuming process and will lock your table for a long time.

Second warning, make sure your current id is acceptable as an identity before trying this. Are you positive that the current data is all integers? Check before doing especially if the field is a varchar field.

Third Warning - I would only do a process like this in single user mode. You do not want users trying to change this data as this is happening. Especially since you will have to drop the foreign key constraints to do it. It is better to do a process like this during off hours and to tell the users that the system is down for maintenance. If you don't you will have unhappy users as there are locks and/or things fail.

"NOTHING is more important in a database than integrity." ESquared