Adding an identity to an existing column

Question

I need to change the primary key of a table to an identity column, and there's already a number of rows in table.

I've got a script to clean up the IDs to ensure they're sequential starting at 1, works fine on my test database.

What's the SQL command to alter the column to have an identity property?

Answer 1

This answer is very similar to This Answer, with little differences.

For example you have 3 tables like this, with two foreign key

DDL:

CREATE TABLE [Table_1]
(
    [id] [int] NOT NULL PRIMARY KEY,
    [Name] [varchar](10) NULL
)
GO

CREATE UNIQUE NONCLUSTERED INDEX Table_1_Index   
ON [Table_1] ([Name]);

CREATE TABLE [Table_2]
(
    [id] [int] NOT NULL  
         FOREIGN KEY REFERENCES [Table_1]([id]) ON UPDATE CASCADE,
    [car] [varchar](10) NULL
)
GO

CREATE TABLE [Table_3]
(
    [id] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,
    [Name] [varchar](10) NULL 
         FOREIGN KEY REFERENCES [Table_1]([Name])
)
GO

INSERT INTO [Table_1] 
VALUES (10000, 'John'), (10001, 'Mike')

INSERT INTO [Table_2] 
VALUES (10000, 'Benz'), (10000, 'Volvo'), (10001, 'BMW')

INSERT INTO [Table_3] 
VALUES ('John')
GO

If you want add identity to id column in Table_1 with dbo schema, and want to start from 10000 with 1 increment, run this query:

DECLARE @Schema SysName = 'dbo'
DECLARE @TableName SysName = 'Table_1'
DECLARE @ColumnName SysName = 'id'
DECLARE @IdentityType VARCHAR(20) = 'IDENTITY(10000,1)'

DECLARE @Table_Name SYSNAME = QUOTENAME(@Schema) + '.' + QUOTENAME(@TableName)
DECLARE @SQLCreateTable NVARCHAR(MAX)
DECLARE @SQLFK NVARCHAR(MAX)
DECLARE @SQL NVARCHAR(MAX)

--get foreign keys
;WITH fk_columns AS 
(
    SELECT
        constraint_object_id, 
        OBJECT_NAME(constraint_object_id) KeyName,  
        OBJECT_NAME(fkc.parent_object_id) FKTable,  
        OBJECT_NAME(fkc.referenced_object_id) PKTable,
        COL_NAME(fkc.parent_object_id, parent_column_id) FKColumn, 
        COL_NAME(fkc.referenced_object_id, referenced_column_id) PKColumn,
        delete_referential_action,
        update_referential_action, 
        is_not_trusted
    FROM
        sys.foreign_key_columns fkc
    JOIN 
        sys.foreign_keys fk ON fk.object_id = fkc.constraint_object_id
    WHERE 
        fkc.referenced_object_id = OBJECT_ID(@Table_Name)
)
SELECT 
    @SQLFK = ISNULL((SELECT (
        SELECT CHAR(13) +
             'ALTER TABLE ' + fk.FKTable + ' WITH' 
            + CASE WHEN fk.is_not_trusted = 1 
                THEN ' NOCHECK' 
                ELSE ' CHECK' 
              END + 
              ' ADD CONSTRAINT [' + fk.KeyName  + '] FOREIGN KEY(' 
              + STUFF((
                SELECT
                    ', [' + k.FKColumn + ']'
                FROM
                    fk_columns k
                WHERE
                    k.constraint_object_id = fk.constraint_object_id
                FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '')
               + ')' +
              ' REFERENCES '+@Table_Name+' ('
              + STUFF((
                SELECT
                    ', [' + k.PKColumn + ']'
                FROM
                    fk_columns k
                WHERE
                    k.constraint_object_id = fk.constraint_object_id
                FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '')
               + ')'
            + CASE 
                WHEN fk.delete_referential_action = 1 THEN ' ON DELETE CASCADE' 
                WHEN fk.delete_referential_action = 2 THEN ' ON DELETE SET NULL'
                WHEN fk.delete_referential_action = 3 THEN ' ON DELETE SET DEFAULT' 
                ELSE '' 
              END
            + CASE 
                WHEN fk.update_referential_action = 1 THEN ' ON UPDATE CASCADE'
                WHEN fk.update_referential_action = 2 THEN ' ON UPDATE SET NULL'
                WHEN fk.update_referential_action = 3 THEN ' ON UPDATE SET DEFAULT'  
                ELSE '' 
              END 
            + CHAR(13) + 'ALTER TABLE ' + fk.FKTable + ' CHECK CONSTRAINT [' + fk.KeyName  + ']' + CHAR(13)
        FROM fk_columns fk WITH (NOWAIT)
        FOR XML PATH(N''), TYPE).value('.', 'NVARCHAR(MAX)')), '')

--drop foreign keys
;WITH fk_columns AS 
(
    SELECT
        OBJECT_NAME(constraint_object_id) KeyName,
        OBJECT_NAME(fkc.parent_object_id) FKTable
    FROM
        sys.foreign_key_columns fkc
    JOIN
        sys.foreign_keys fk ON fk.object_id = fkc.constraint_object_id
    WHERE
        fkc.referenced_object_id = OBJECT_ID(@Table_Name)
)
SELECT @SQL = ISNULL((SELECT (
        SELECT
             CHAR(13) + 'ALTER TABLE ' + fk.FKTable +
             ' DROP CONSTRAINT [' + fk.KeyName + ']' + CHAR(13)
        FROM
            fk_columns fk WITH (NOWAIT)
        FOR XML PATH(N''), TYPE).value('.', 'NVARCHAR(MAX)')), '')
EXECUTE sp_executesql @SQL

--find not exist table name
DECLARE @i INT = 1
WHILE OBJECT_ID(QUOTENAME(@Schema) + '.' +QUOTENAME(@TableName) + '_Temp' + CAST(@i AS varchar), 'U') IS NOT NULL
    SET @i += 1
DECLARE @TempName SysName = @TableName + '_Temp' + CAST(@i AS VARCHAR)
DECLARE @Temp_Name SysName = QUOTENAME(@Schema) + '.' +QUOTENAME(@TableName + '_Temp' + CAST(@i AS VARCHAR))

--create temp table like table code
DECLARE @object_id INT = OBJECT_ID(@Table_Name)
;WITH index_column AS 
(
    SELECT 
        ic.[object_id],
        ic.index_id,
        ic.is_descending_key,
        ic.is_included_column,
        c.name
    FROM
        sys.index_columns ic WITH (NOWAIT)
    JOIN
        sys.columns c WITH (NOWAIT) ON ic.[object_id] = c.[object_id] AND ic.column_id = c.column_id
    WHERE
        ic.[object_id] = @object_id
),
fk_columns AS 
(
     SELECT 
        k.constraint_object_id,
        cname = c.name,
        rcname = rc.name
    FROM
        sys.foreign_key_columns k WITH (NOWAIT)
    JOIN
        sys.columns rc WITH (NOWAIT) ON rc.[object_id] = k.referenced_object_id AND rc.column_id = k.referenced_column_id 
    JOIN
        sys.columns c WITH (NOWAIT) ON c.[object_id] = k.parent_object_id AND c.column_id = k.parent_column_id
    WHERE
        k.parent_object_id = @object_id
)
SELECT @SQLCreateTable = 'CREATE TABLE ' + @Temp_Name + CHAR(13) + '(' + CHAR(13) + STUFF((
    SELECT
        CHAR(9) + ', [' + c.name + '] ' + 
        CASE WHEN c.is_computed = 1
            THEN 'AS ' + cc.[definition] 
            ELSE UPPER(tp.name) + 
                CASE WHEN tp.name IN ('varchar', 'char', 'varbinary', 'binary', 'text')
                       THEN '(' + CASE WHEN c.max_length = -1 THEN 'MAX' ELSE CAST(c.max_length AS VARCHAR(5)) END + ')'
                     WHEN tp.name IN ('nvarchar', 'nchar', 'ntext')
                       THEN '(' + CASE WHEN c.max_length = -1 THEN 'MAX' ELSE CAST(c.max_length / 2 AS VARCHAR(5)) END + ')'
                     WHEN tp.name IN ('datetime2', 'time2', 'datetimeoffset') 
                       THEN '(' + CAST(c.scale AS VARCHAR(5)) + ')'
                    WHEN tp.name IN ('decimal', 'numeric')
                       THEN '(' + CAST(c.[precision] AS VARCHAR(5)) + ',' + CAST(c.scale AS VARCHAR(5)) + ')'
                    ELSE ''
                END +
                CASE WHEN c.collation_name IS NOT NULL THEN ' COLLATE ' + c.collation_name ELSE '' END +
                CASE WHEN c.name = @ColumnName THEN ' ' +@IdentityType ELSE '' END +
                CASE WHEN c.is_nullable = 1 and c.name <> @ColumnName THEN ' NULL' ELSE ' NOT NULL' END +
                CASE WHEN dc.[definition] IS NOT NULL and c.name <> @ColumnName THEN ' DEFAULT' + dc.[definition] ELSE '' END
            END + CHAR(13)
    FROM
        sys.columns c WITH (NOWAIT)
    JOIN 
        sys.types tp WITH (NOWAIT) ON c.user_type_id = tp.user_type_id
    LEFT JOIN
        sys.computed_columns cc WITH (NOWAIT) ON c.[object_id] = cc.[object_id] AND c.column_id = cc.column_id
    LEFT JOIN
        sys.default_constraints dc WITH (NOWAIT) ON c.default_object_id != 0 AND c.[object_id] = dc.parent_object_id AND c.column_id = dc.parent_column_id
    LEFT JOIN
        sys.identity_columns ic WITH (NOWAIT) ON c.is_identity = 1 AND c.[object_id] = ic.[object_id] AND c.column_id = ic.column_id
    WHERE
        c.[object_id] = @object_id
    ORDER BY
        c.column_id
    FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, CHAR(9) + ' ')
    + ISNULL((SELECT CHAR(9) + ', CONSTRAINT [' + k.name + '] PRIMARY KEY (' + 
                    (SELECT STUFF((
                         SELECT ', [' + c.name + '] ' + CASE WHEN ic.is_descending_key = 1 THEN 'DESC' ELSE 'ASC' END
                         FROM
                            sys.index_columns ic WITH (NOWAIT)
                         JOIN
                            sys.columns c WITH (NOWAIT) ON c.[object_id] = ic.[object_id] AND c.column_id = ic.column_id
                         WHERE
                            ic.is_included_column = 0
                            AND ic.[object_id] = k.parent_object_id 
                            AND ic.index_id = k.unique_index_id     
                         FOR XML PATH(N''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, ''))
            + ')' + CHAR(13)
            FROM
                sys.key_constraints k WITH (NOWAIT)
            WHERE
                k.parent_object_id = @object_id 
                AND k.[type] = 'PK'), '') + ')'  + CHAR(13)
    + ISNULL((SELECT (
        SELECT CHAR(13) +
             'ALTER TABLE ' + @Temp_Name + ' WITH' 
            + CASE WHEN fk.is_not_trusted = 1 
                THEN ' NOCHECK' 
                ELSE ' CHECK' 
              END + 
              ' ADD CONSTRAINT [' + fk.name  + '] FOREIGN KEY(' 
              + STUFF((
                SELECT
                    ', [' + k.cname + ']'
                FROM
                    fk_columns k
                WHERE
                    k.constraint_object_id = fk.[object_id]
                FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '')
               + ')' +
              ' REFERENCES [' + SCHEMA_NAME(ro.[schema_id]) + '].[' + ro.name + '] ('
              + STUFF((
                SELECT
                    ', [' + k.rcname + ']'
                FROM
                    fk_columns k
                WHERE
                    k.constraint_object_id = fk.[object_id]
                FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '')
               + ')'
            + CASE 
                WHEN fk.delete_referential_action = 1 THEN ' ON DELETE CASCADE' 
                WHEN fk.delete_referential_action = 2 THEN ' ON DELETE SET NULL'
                WHEN fk.delete_referential_action = 3 THEN ' ON DELETE SET DEFAULT' 
                ELSE '' 
              END
            + CASE 
                WHEN fk.update_referential_action = 1 THEN ' ON UPDATE CASCADE'
                WHEN fk.update_referential_action = 2 THEN ' ON UPDATE SET NULL'
                WHEN fk.update_referential_action = 3 THEN ' ON UPDATE SET DEFAULT'  
                ELSE '' 
              END 
            + CHAR(13) + 'ALTER TABLE ' + @Temp_Name + ' CHECK CONSTRAINT [' + fk.name  + ']' + CHAR(13)
        FROM sys.foreign_keys fk WITH (NOWAIT)
        JOIN sys.objects ro WITH (NOWAIT) ON ro.[object_id] = fk.referenced_object_id
        WHERE fk.parent_object_id = @object_id
        FOR XML PATH(N''), TYPE).value('.', 'NVARCHAR(MAX)')), '')
    + ISNULL(((SELECT
         CHAR(13) + 'CREATE' + CASE WHEN i.is_unique = 1 THEN ' UNIQUE' ELSE '' END
                + CASE i.type WHEN 1 THEN ' CLUSTERED' ELSE ' NONCLUSTERED' END
                + ' INDEX [' + i.name + '] ON ' + @Temp_Name + ' (' +
                STUFF((
                SELECT ', [' + c.name + ']' + CASE WHEN c.is_descending_key = 1 THEN ' DESC' ELSE ' ASC' END
                FROM
                    index_column c
                WHERE
                    c.is_included_column = 0
                    AND c.index_id = i.index_id
                FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '') + ')'  
                + ISNULL(CHAR(13) + 'INCLUDE (' + 
                    STUFF((
                    SELECT
                        ', [' + c.name + ']'
                    FROM
                        index_column c
                    WHERE
                        c.is_included_column = 1
                        AND c.index_id = i.index_id
                    FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '') + ')', '')  + CHAR(13)
        FROM
            sys.indexes i WITH (NOWAIT)
        WHERE
            i.[object_id] = @object_id
            AND i.is_primary_key = 0
        FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)')
    ), '')

--rename primary key constaint on column from table
DECLARE @PrimaryName SYSNAME
SELECT @PrimaryName=i.name
    FROM
        sys.index_columns ic WITH (NOWAIT)
    JOIN
        sys.indexes i WITH (NOWAIT) ON i.object_id = ic.object_id and i.index_id = ic.index_id
    WHERE
        i.object_id = OBJECT_ID(@Table_Name) and Col_Name(ic.object_id, column_id) = @ColumnName and is_primary_key = 1
SET @SQL = N'EXEC sp_rename ' + @PrimaryName + ', ' + 'ThisMustNotBeExist'+ @PrimaryName + ', N''OBJECT'''
EXECUTE sp_executesql @SQL
--rename index on column from table
DECLARE @IndexName SYSNAME
SELECT
    @IndexName = i.name
FROM
    sys.index_columns ic WITH (NOWAIT)
JOIN
    sys.indexes i WITH (NOWAIT) ON i.object_id = ic.object_id AND i.index_id = ic.index_id
WHERE
    i.object_id = OBJECT_ID(@Table_Name)
    AND Col_Name(ic.object_id, column_id) = @ColumnName
    AND is_primary_key = 0
SET @SQL = N'EXEC sp_rename ' + @Table_Name + '.' + @IndexName + ', ' + 'ThisMustNotBeExist'+ @IndexName + ', N''INDEX''' 
EXECUTE sp_executesql @SQL

--run create temp table like table code
EXECUTE sp_executesql @SQLCreateTable

--Move table data to temp data
SET @SQL = N'ALTER TABLE '+ @Table_Name + ' SWITCH TO ' + @Temp_Name
EXECUTE sp_executesql @SQL

--check clean up 
SET @SQL = N'IF EXISTS (SELECT * FROM '+ @Table_Name + ') THROW 50000, ''Table is not clear'', 1'
EXECUTE sp_executesql @SQL

--drop table
SET @SQL = N'DROP TABLE '+ @Table_Name
EXECUTE sp_executesql @SQL

--rename temp table to table
EXEC sys.sp_rename @Temp_Name, @TableName, 'OBJECT';

--create foreign keys
EXECUTE sp_executesql @SQLFK

DBCC CHECKIDENT(@TableName, RESEED)

Note

• This code only copy keys and indexes, if you have other things on that table, you loose them after running this code

If you want copy triggers or extended property of the table too, check this answer and add @SQL2 and @SQL3 to @SQLCreateTable before executing it

Answer 2

In my case there were a lot of tables without identity so copying and recreating were going to take too long. I found an easier way to do it if you are using SQL Server Management Studio:

1. Open Tools -> Options -> Designers -> Table and Database Designers.
2. Uncheck the "Prevent Saving Changes That Prevent Table Recreation" checkbox in order not to drop the table and recreate it when changing the table design.
3. Save the changes.
4. Right click on the table and click Design.
5. Choose the column, open the properties and select "Yes" from the Identity dropdown.

The table is not dropped and the data inside the table and the dependencies remains the same.

Answer 3

Consider to use SEQUENCE instead of IDENTITY.

In SQL Server 2014 (I don't know about lower versions) you can do this simply, using sequence.

CREATE SEQUENCE sequence_name START WITH here_higher_number_than_max_existed_value_in_column INCREMENT BY 1;

ALTER TABLE table_name ADD CONSTRAINT constraint_name DEFAULT NEXT VALUE FOR sequence_name FOR column_name

From here: Sequence as default value for a column

Answer 4

In SQL 2005 and above, there's a trick to solve this problem without changing the table's data pages. This is important for large tables where touching every data page can take minutes or hours. The trick also works even if the identity column is a primary key, is part of a clustered or non-clustered index, or other gotchas which can trip up the the simpler "add/remove/rename column" solution.

Here's the trick: you can use SQL Server's ALTER TABLE...SWITCH statement to change the schema of a table without changing the data, meaning you can replace a table with an IDENTITY with an identical table schema, but without an IDENTITY column. The same trick works to add IDENTITY to an existing column.

Normally, ALTER TABLE...SWITCH is used to efficiently replace a full partition in a partitioned table with a new, empty partition. But it can also be used in non-partitioned tables too.

I've used this trick to convert, in under 5 seconds, a column of a 2.5 billion row table from IDENTITY to a non-IDENTITY (in order to run a multi-hour query whose query plan worked better for non-IDENTITY columns), and then restored the IDENTITY setting, again in less than 5 seconds.

Here's a code sample of how it works.

 CREATE TABLE Test
 (
   id int identity(1,1),
   somecolumn varchar(10)
 );
  
 INSERT INTO Test VALUES ('Hello');
 INSERT INTO Test VALUES ('World');
  
 -- copy the table. use same schema, but no identity
 CREATE TABLE Test2
 (
   id int NOT NULL,
   somecolumn varchar(10)
 );
  
 ALTER TABLE Test SWITCH TO Test2;

 -- drop the original (now empty) table
 DROP TABLE Test;

 -- rename new table to old table's name
 EXEC sp_rename 'Test2','Test';
  
 -- update the identity seed
 DBCC CHECKIDENT('Test');

 -- see same records
 SELECT * FROM Test; 

This is obviously more involved than the solutions in other answers, but if your table is large this can be a real life-saver. There are some caveats:

• As far as I know, identity is the only thing you can change about your table's columns with this method. Adding/removing columns, changing nullability, etc. isn't allowed.
• You'll need to drop foriegn keys before you do the switch and restore them after.
• Same for WITH SCHEMABINDING functions, views, etc.
• new table's indexes need to match exactly (same columns, same order, etc.)
• Old and new tables need to be on the same filegroup.
• Only works on SQL Server 2005 or later
• I previously believed that this trick only works on the Enterprise or Developer editions of SQL Server (because partitions are only supported in Enterprise and Developer versions), but Mason G. Zhwiti in his comment below says that it also works in SQL Standard Edition too. I assume this means that the restriction to Enterprise or Developer doesn't apply to ALTER TABLE...SWITCH.

There's a good article on TechNet detailing the requirements above.

UPDATE - Eric Wu had a comment below that adds important info about this solution. Copying it here to make sure it gets more attention:

There's another caveat here that is worth mentioning. Although the new table will happily receive data from the old table, and all the new rows will be inserted following a identity pattern, they will start at 1 and potentially break if the said column is a primary key. Consider running DBCC CHECKIDENT('<newTableName>') immediately after switching. See msdn.microsoft.com/en-us/library/ms176057.aspx for more info.

If the table is actively being extended with new rows (meaning you don't have much if any downtime between adding IDENTITY and adding new rows, then instead of DBCC CHECKIDENT you'll want to manually set the identity seed value in the new table schema to be larger than the largest existing ID in the table, e.g. IDENTITY (2435457, 1). You might be able to include both the ALTER TABLE...SWITCH and the DBCC CHECKIDENT in a transaction (or not-- haven't tested this) but seems like setting the seed value manually will be easier and safer.

Obviously, if no new rows are being added to the table (or they're only added occasionally, like a daily ETL process) then this race condition won't happen so DBCC CHECKIDENT is fine.

Answer 5

You can't alter the existing columns for identity.

You have 2 options,

1. Create a new table with identity & drop the existing table

2. Create a new column with identity & drop the existing column

Approach 1. (New table) Here you can retain the existing data values on the newly created identity column. Note that you will lose all data if 'if not exists' is not satisfied, so make sure you put the condition on the drop as well!

CREATE TABLE dbo.Tmp_Names
    (
      Id int NOT NULL
             IDENTITY(1, 1),
      Name varchar(50) NULL
    )
ON  [PRIMARY]
go

SET IDENTITY_INSERT dbo.Tmp_Names ON
go

IF EXISTS ( SELECT  *
            FROM    dbo.Names ) 
    INSERT  INTO dbo.Tmp_Names ( Id, Name )
            SELECT  Id,
                    Name
            FROM    dbo.Names TABLOCKX
go

SET IDENTITY_INSERT dbo.Tmp_Names OFF
go

DROP TABLE dbo.Names
go

Exec sp_rename 'Tmp_Names', 'Names'

Approach 2 (New column) You can’t retain the existing data values on the newly created identity column, The identity column will hold the sequence of number.

Alter Table Names
Add Id_new Int Identity(1, 1)
Go

Alter Table Names Drop Column ID
Go

Exec sp_rename 'Names.Id_new', 'ID', 'Column'

See the following Microsoft SQL Server Forum post for more details:

How to alter column to identity(1,1)

Answer 6

If you happen to be using Visual Studio 2017+

1. In Server Object Explorer right-click on your table and select "view code"
2. Add the modifier "IDENTITY" to your column
3. Update

This will do it all for you.

Answer 7

As I understood in normal cases we are creating a table with Primary key which is having Identity property
So Rename or Delete a column which is associated with Primary Key constraint will not be possible because constraint Rules are validating column structure.
Tto achieve this we have to process some steps in the following way:
Let us assume TableName = 'Employee' and ColumnName = 'EmployeeId'

1. Add new column 'EmployeeId_new' in the 'Employee' table
ALTER TABLE Employee ADD EmployeeId_new INT IDENTITY(1,1)

2. Now remove column 'EmployeeId' from 'Employee' table
   ALTER TABLE Employee DROP COLUMN EmployeeId

3. This will throw error because of Primary Key Constraint rules are applicable and validating column structure.
   *### 'Msg 5074, Level 16, State 1, Line 1 The object [PK_dbo.Employee] is dependent on colmn [EmployeeId].'###

4. So we have to remove the Primary Key constraint first from the table 'Employee' then we can remove the column
   ALTER TABLE Employee DROP constraint [PK_dbo.Employee]

5. Now we can remove the column 'EmployeeId' from 'Employee' table as did in the previous step where we got error
   ALTER TABLE Employee DROP COLUMN EmployeeId

6. Now Column 'EmployeeId' removed from table So we will Rename the newly added new column 'EmployeeId_new' with 'EmployeeId'
   sp_rename 'Employee.EmployeeId', 'EmployeeId_new', 'COLUMN'

7. To rearrange the table in the same form as It was, we have to add Primary Key Constraint for the column 'EmployeeId'
   ALTER TABLE Employee add constraint [PK_dbo.Employee] primary key (EmployeeId)

8. Now the table 'Employee' with 'EmployeeId' is modified for Identity rules along with existing primary key constraint

Answer 8

I'm a java developer that happened to get on a team without a DBA and one where as a developer, I can't get DBA rights. I was tasked with moving an entire schema between two databases, so without having a DBA, I had to do it and do it by running scripts, not being able to use the GUI in SQL Server 2008 because I didn't have admin privileges.

Everything was moved without issue, however, when running a stored procedure on the new schema.table, I found I lost the identity field in a table. I double checked the script that created the table and it was there, however, SQL Server didn't get it when I ran the script. I was told later by a DBA that he had seen this same problem before.

In any event, for SQL Server 2008, these are the steps I took to get this resolved and they worked, so I'm posting this here in the hopes it will be a help to someone. This is what I did as I had FK dependencies on another table that made this more difficult:

I used this query to verify the identity was indeed missing and to view dependencies on the table.

1.) Find statistics on a table:

exec sp_help 'dbo.table_name_old';

2.) Create a duplicate, identical new table, except add an identity field on the PK field where it had been before.

3.) Disable the identity to move data.

SET IDENTITY_INSERT dbo.table_name ON 

4.) Transfer the data.

INSERT INTO dbo.table_name_new
(
field1, field2, etc...
)
SELECT 
field1, field2, etc...
FROM 
dbo.table_name_old;

5.) Verify the data is there.

SELECT * FROM dbo.table_name_new

6.) Re-enable the identity.

SET IDENTITY_INSERT ToyRecP.ToyAwards.lkpFile_New OFF

7.) This is the best script I found to get all the FK relationships to verify which table(s) the original table references as dependencies and I came across many, so it is a keeper!

SELECT f.name AS ForeignKey,
   OBJECT_NAME(f.parent_object_id) AS TableName,
   COL_NAME(fc.parent_object_id, fc.parent_column_id) AS ColumnName,
   OBJECT_NAME (f.referenced_object_id) AS ReferenceTableName,
   COL_NAME(fc.referenced_object_id, fc.referenced_column_id) AS ReferenceColumnName
FROM sys.foreign_keys AS f
INNER JOIN sys.foreign_key_columns AS fc
   ON f.OBJECT_ID = fc.constraint_object_id
   ORDER BY ReferenceTableName;

8.) Make sure you have all the PK and FK scripts for all the tables involved, before this next step.

9.) You can right-click on each key and script this using SQL Server 2008

10.) Drop the FK(s) from the dependency table(s) using this syntax:

ALTER TABLE [dbo].[table_name] DROP CONSTRAINT [Name_of_FK]

11.) Drop the original table:

DROP TABLE dbo.table_name_old;

13.) These next steps rely on the scripts you created in SQL Server 2008 in step 9.

--Add the PK to the new table.

--Add the FK to the new table.

--Add the FK's back to the dependency table.

14.) Verify everything is correct and complete. I used the GUI to look at the tables.

15.) Rename the new table to the original tables name.

exec sp_RENAME '[Schema_Name.OldTableName]' , '[NewTableName]';

Finally, everything worked!

Answer 9

To modify the identity properties for a column:

• In Server Explorer, right-click the table with identity properties you want to modify and click Open Table Definition. The table opens in Table Designer.
• Clear the Allow nulls check box for the column you want to change.
• In the Column Properties tab, expand the Identity Specification property.
• Click the grid cell for the Is Identity child property and choose Yes from the drop-down list.
• Type a value in the Identity Seed cell. This value will be assigned to the first row in the table. The value 1 will be assigned by default.

That's it, and it worked for me

Answer 10

Right click on table name in Object Explorer. You will get some options. Click on 'Design'. A new tab will be opened for this table. You can add Identity constraint here in 'Column Properties'.

Answer 11

As per my current condition, I follow this approach. I want to give identity to a primary table after data inserted via script.

As I want to append identity, so it always start from 1 to End of record count that I want.

--first drop column and add with identity
ALTER TABLE dbo.tblProductPriceList drop column ID 
ALTER TABLE dbo.tblProductPriceList add ID INT IDENTITY(1,1)

--then add primary key to that column (exist option you can ignore)
IF  NOT EXISTS (SELECT * FROM sys.key_constraints  WHERE object_id = OBJECT_ID(N'[dbo].[PK_tblProductPriceList]') AND parent_object_id = OBJECT_ID(N'[dbo].[tblProductPriceList]'))
    ALTER TABLE [tblProductPriceList] ADD PRIMARY KEY (id)
GO

This will create the same primary key column with identity

I used this links : https://blog.sqlauthority.com/2014/10/11/sql-server-add-auto-incremental-identity-column-to-table-after-creating-table/

Add primary key to existing table

Answer 12

You cannot alter a column to be an IDENTITY column. What you'll need to do is create a new column which is defined as an IDENTITY from the get-go, then drop the old column, and rename the new one to the old name.

ALTER TABLE (yourTable) ADD NewColumn INT IDENTITY(1,1)

ALTER TABLE (yourTable) DROP COLUMN OldColumnName

EXEC sp_rename 'yourTable.NewColumn', 'OldColumnName', 'COLUMN'

Marc

Answer 13

Simple explanation

Rename the existing column using sp_RENAME

EXEC sp_RENAME 'Table_Name.Existing_ColumnName' , 'New_ColumnName', 'COLUMN'

Example for Rename :

The existing column UserID is renamed as OldUserID

EXEC sp_RENAME 'AdminUsers.UserID' , 'OldUserID', 'COLUMN'

Then add a new column using alter query to set as primary key and identity value

ALTER TABLE TableName ADD Old_ColumnName INT NOT NULL PRIMARY KEY IDENTITY(1,1)

Example for Set Primary key

The new created column name is UserID

ALTER TABLE Users ADD UserID INT NOT NULL PRIMARY KEY IDENTITY(1,1)

then Drop the Renamed Column

ALTER TABLE Table_Name DROP COLUMN Renamed_ColumnName

Example for Drop renamed column

ALTER TABLE Users DROP COLUMN OldUserID

Now we've adding a primarykey and identity to the existing column on the table.

Answer 14

generates a script for all tables with primary key = bigint which do not have an identity set; this will return a list of generated scripts with each table;

SET NOCOUNT ON;

declare @sql table(s varchar(max), id int identity)

DECLARE @table_name nvarchar(max),
        @table_schema nvarchar(max);

DECLARE vendor_cursor CURSOR FOR 
SELECT
  t.name, s.name
FROM sys.schemas AS s
INNER JOIN sys.tables AS t
  ON s.[schema_id] = t.[schema_id]
WHERE EXISTS (
    SELECT
    [c].[name]
    from sys.columns [c]
    join sys.types [y] on [y].system_type_id = [c].system_type_id
    where [c].[object_id] = [t].[object_id] and [y].name = 'bigint' and [c].[column_id] = 1
) and NOT EXISTS 
(
  SELECT 1 FROM sys.identity_columns
    WHERE [object_id] = t.[object_id]
) and exists (
    select 1 from sys.indexes as [i] 
    inner join sys.index_columns as [ic]  ON  i.OBJECT_ID = ic.OBJECT_ID AND i.index_id = ic.index_id
    where object_name([ic].[object_id]) = [t].[name]
)
OPEN vendor_cursor

FETCH NEXT FROM vendor_cursor 
INTO @table_name, @table_schema

WHILE @@FETCH_STATUS = 0
BEGIN

DELETE FROM @sql

declare @pkname varchar(100),
    @pkcol nvarchar(100)

SELECT  top 1
        @pkname = i.name,
        @pkcol = COL_NAME(ic.OBJECT_ID,ic.column_id)
FROM    sys.indexes AS [i]
INNER JOIN sys.index_columns AS [ic] ON  i.OBJECT_ID = ic.OBJECT_ID AND i.index_id = ic.index_id
WHERE   i.is_primary_key = 1 and OBJECT_NAME(ic.OBJECT_ID) = @table_name

declare @q nvarchar(max) = 'SELECT  '+@pkcol+' FROM ['+@table_schema+'].['+@table_name+'] ORDER BY '+@pkcol+' DESC'

DECLARE @ident_seed nvarchar(max) -- Change this to the datatype that you are after
SET @q = REPLACE(@q, 'SELECT', 'SELECT TOP 1 @output = ')
EXEC sp_executeSql @q, N'@output bigint OUTPUT', @ident_seed OUTPUT

insert into  @sql(s) values ('BEGIN TRANSACTION')
insert into  @sql(s) values ('BEGIN TRY')

-- create statement
insert into  @sql(s) values ('create table ['+@table_schema+'].[' + @table_name + '_Temp] (')

-- column list
insert into @sql(s) 
select 
    '  ['+[c].[name]+'] ' +
    y.name + 

    (case when [y].[name] like '%varchar' then
    coalesce('('+(case when ([c].[max_length] < 0 or [c].[max_length] >= 1024) then 'max' else cast([c].max_length as varchar) end)+')','')
    else '' end)

     + ' ' +
    case when [c].name = @pkcol then 'IDENTITY(' +COALESCE(@ident_seed, '1')+',1)' else '' end + ' ' +
    ( case when c.is_nullable = 0 then 'NOT ' else '' end ) + 'NULL ' + 
    coalesce('DEFAULT ('+(
        REPLACE(
            REPLACE(
                LTrim(
                    RTrim(
                        REPLACE(
                            REPLACE(
                                REPLACE(
                                    REPLACE(
                                        LTrim(
                                            RTrim(
                                                REPLACE(
                                                    REPLACE(
                                                        object_definition([c].default_object_id)
                                                    ,' ','~')
                                                ,')',' ')
                                            )
                                        )
                                    ,' ','*')
                                ,'~',' ')
                            ,' ','~')
                        ,'(',' ')
                    )
                )
            ,' ','*')
        ,'~',' ')
    ) +
    case when object_definition([c].default_object_id) like '%get%date%' then '()' else '' end
    +
    ')','') + ','
 from sys.columns c
 JOIN sys.types y ON y.system_type_id = c.system_type_id
  where OBJECT_NAME(c.[object_id]) = @table_name and [y].name != 'sysname'
 order by [c].column_id


 update @sql set s=left(s,len(s)-1) where id=@@identity

-- closing bracket
insert into @sql(s) values( ')' )

insert into @sql(s) values( 'SET IDENTITY_INSERT ['+@table_schema+'].['+@table_name+'_Temp] ON')

declare @cols nvarchar(max)
SELECT @cols = STUFF(
    (
        select ',['+c.name+']'
        from sys.columns c
        JOIN sys.types y ON y.system_type_id = c.system_type_id
        where c.[object_id] = OBJECT_ID(@table_name)
        and [y].name != 'sysname'
        and [y].name != 'timestamp'
        order by [c].column_id
        FOR XML PATH ('')
     )
    , 1, 1, '')

insert into @sql(s) values( 'IF EXISTS(SELECT * FROM ['+@table_schema+'].['+@table_name+'])')
insert into @sql(s) values( 'EXEC(''INSERT INTO ['+@table_schema+'].['+@table_name+'_Temp] ('+@cols+')')
insert into @sql(s) values( 'SELECT '+@cols+' FROM ['+@table_schema+'].['+@table_name+']'')')

insert into @sql(s) values( 'SET IDENTITY_INSERT ['+@table_schema+'].['+@table_name+'_Temp] OFF')


insert into @sql(s) values( 'DROP TABLE ['+@table_schema+'].['+@table_name+']')

insert into @sql(s) values( 'EXECUTE sp_rename N''['+@table_schema+'].['+@table_name+'_Temp]'', N'''+@table_name+''', ''OBJECT''')

if ( @pkname is not null ) begin
    insert into @sql(s) values('ALTER TABLE ['+@table_schema+'].['+@table_name+'] ADD CONSTRAINT ['+@pkname+'] PRIMARY KEY CLUSTERED (')
    insert into @sql(s)
        select '  ['+COLUMN_NAME+'] ASC,' from information_schema.key_column_usage
        where constraint_name = @pkname
        GROUP BY COLUMN_NAME, ordinal_position
        order by ordinal_position

    -- remove trailing comma
    update @sql set s=left(s,len(s)-1) where id=@@identity
    insert into @sql(s) values ('  )')
end

insert into  @sql(s) values ('--Run your Statements')
insert into  @sql(s) values ('COMMIT TRANSACTION')
insert into  @sql(s) values ('END TRY')
insert into  @sql(s) values ('BEGIN CATCH')
insert into  @sql(s) values ('        ROLLBACK TRANSACTION')
insert into  @sql(s) values ('        DECLARE @Msg NVARCHAR(MAX)  ')
insert into  @sql(s) values ('        SELECT @Msg=ERROR_MESSAGE() ')
insert into  @sql(s) values ('        RAISERROR(''Error Occured: %s'', 20, 101,@msg) WITH LOG')
insert into  @sql(s) values ('END CATCH')

declare @fqry nvarchar(max)

-- result!
SELECT @fqry = (select char(10) + s from @sql order by id FOR XML PATH (''))


SELECT @table_name as [Table_Name], @fqry as [Generated_Query]
PRINT 'Table: '+@table_name
EXEC sp_executeSql @fqry

    FETCH NEXT FROM vendor_cursor 
    INTO @table_name, @table_schema
END 
CLOSE vendor_cursor;
DEALLOCATE vendor_cursor;

Answer 15

Basically there are four logical steps.

1. Create a new Identity column. Turn on Insert Identity for this new column.

2. Insert the data from the source column (the column you wished to convert to Identity) to this new column.

3. Turn off the Insert Identity for the new column.

4. Drop your source column & rename the new column to the name of the source column.

There may be some more complexities like working across multiple servers etc.

Please refer the following article for the steps (using ssms & T-sql). These steps are intended for beginners with less grip on T-SQL.

http://social.technet.microsoft.com/wiki/contents/articles/23816.how-to-convert-int-column-to-identity-in-the-ms-sql-server.aspx

Answer 16

There is cool solution described here: SQL SERVER – Add or Remove Identity Property on Column

In short edit manually your table in SQL Manager, switch the identity, DO NOT SAVE changes, just show the script which will be created for the changes, copy it and use it later.

It is huge time saver, because it (the script) contains all the foreign keys, indices, etc. related to the table you change. Writting this manually... God forbid.

Answer 17

If the original poster was actually wanting to set an existing column to be a PRIMARY KEY for the table and actually did not need the column to be an IDENTITY column (two different things) then this can be done via t-SQL with:

ALTER TABLE [YourTableName]
ADD CONSTRAINT [ColumnToSetAsPrimaryKey] PRIMARY KEY ([ColumnToSetAsPrimaryKey])

Note the parenthesis around the column name after the PRIMARY KEY option.

Although this post is old and I am making an assumption about the requestors need, I felt this additional information could be helpful to users encountering this thread as I believe the conversation could lead one to believe that an existing column can not be set to be a primary key without adding it as a new column first which would be incorrect.

Answer 18

you can't do it like that, you need to add another column, drop the original column and rename the new column or or create a new table, copy the data in and drop the old table followed by renaming the new table to the old table

if you use SSMS and set the identity property to ON in the designer here is what SQL Server does behind the scenes. So if you have a table named [user] this is what happens if you make UserID and identity

BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
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

GO
CREATE TABLE dbo.Tmp_User
    (
    UserID int NOT NULL IDENTITY (1, 1),
    LastName varchar(50) NOT NULL,
    FirstName varchar(50) NOT NULL,
    MiddleInitial char(1) NULL

    )  ON [PRIMARY]
GO

SET IDENTITY_INSERT dbo.Tmp_User ON
GO
IF EXISTS(SELECT * FROM dbo.[User])
 EXEC('INSERT INTO dbo.Tmp_User (UserID, LastName, FirstName, MiddleInitial)
    SELECT UserID, LastName, FirstName, MiddleInitialFROM dbo.[User] TABLOCKX')
GO
SET IDENTITY_INSERT dbo.Tmp_User OFF
GO

GO
DROP TABLE dbo.[User]
GO
EXECUTE sp_rename N'dbo.Tmp_User', N'User', 'OBJECT'
GO
ALTER TABLE dbo.[User] ADD CONSTRAINT
    PK_User PRIMARY KEY CLUSTERED 
    (
    UserID
    ) ON [PRIMARY]

GO
COMMIT

Having said that there is a way to hack the system table to accomplish it by setting the bitwise value but that is not supported and I wouldn't do it

Answer 19

By design there is no simple way to turn on or turn off the identity feature for an existing column. The only clean way to do this is to create a new column and make it an identity column or create a new table and migrate your data.

If we use SQL Server Management Studio to get rid of the identity value on column "id", a new temporary table is created, the data is moved to the temporary table, the old table is dropped and the new table is renamed.

Use Management Studio to make the change and then right click in the designer and select "Generate Change Script".

You will see that this is what SQL server in doing in the background.

Answer 20

There isn't one, sadly; the IDENTITY property belongs to the table rather than the column.

The easier way is to do it in the GUI, but if this isn't an option, you can go the long way around of copying the data, dropping the column, re-adding it with identity, and putting the data back.

See here for a blow-by-blow account.

Answer 21

I don't believe you can alter an existing column to be an identity column using tsql. However, you can do it through the Enterprise Manager design view.

Alternatively you could create a new row as the identity column, drop the old column, then rename your new column.

ALTER TABLE FooTable
ADD BarColumn INT IDENTITY(1, 1)
               NOT NULL
               PRIMARY KEY CLUSTERED