SQL Server Performance Forum – Threads Archive
Adding identity constraint
How can i add an identity constraint to a column in the existing table? ThanksNitin
alter table tableName add id int identity Madhivanan
Identity is a column proprty, not a constraint. If you also want your column to be the table’s primary key, you need to do ALTER TABLE tablename
ADD tableid INT IDENTITY (1,1) PRIMARY KEY CLUSTERED | NONCLUSTERED or explicitly create a unique index or constraint on it. Tom Pullen
DBA, Oxfam GB
I want to add a contraint to a column that is already existing. E.g., Table -> tableName: {id1,id2} Now i want to make id1 as identity. ‘alter table tableName add id1 int identity’
gives an error as the column already exists in the table.
Through EM goto design of table.
Select Int Column properties.
And select the field as table identity column
When there is no unique constraint on the existing column, make sure there are no duplicates before doing this. It would be best to add a new column that has the identity property, then drop the existing column, and finally rename the identity column.
So is this the limitation that i cannot do it with the SQL commands.
Yes. If you do what ranjitjain said, it will copy the table to a temp table, delete the table, recreate the table with an identity column, then copy the data back. You can script it out if you want to test this. What Adriaan said is usually the best solution for this. That can, of course, be scripted. Anything that EM does can be scripted, and usually be done more efficiently. MeanOldDBA
[email protected] When life gives you a lemon, fire the DBA.
Yes what Adriaan said is a better solution but will not be useful if this column is a foreign key in another table.
You can drop and recreate foreign key.
If the existing column is referenced as a foreign key (in a relationship) then by definition it cannot have duplicate values. Elaborating on what mmarovic said:
(1) add the identity column,
(2) drop the foreign key,
(3) update the FK field on the subtable to the identity column value for the old FK value,
(4) drop the old column that was used as FK,
(5) rename the new column,
(6) restore the foreign key.
]]>