0

I have a table Employees :

  • EmpId int not null
  • EmpName nvarchar
  • ...

However this table is referenced by other tables via a foreign key, i.e. the EmpId is a foreign key in other tables.

This code doesn't work : 

USE MyDatabase

ALTER TABLE [MyDatabase].[EmployeesSchema].[Employees] 
DROP CONSTRAINT PK_Employees

ALTER TABLE [MyDatabase].[EmployeesSchema].[Employees] 
DROP COLUMN EmpId

ALTER TABLE [MyDatabase].[EmployeesSchema].[Employees] 
   ADD TempColumn INT IDENTITY(1,1)

ALTER TABLE [MyDatabase].[EmployeesSchema].[Employees]
   ADD CONSTRAINT PK_Employees PRIMARY KEY (TempColumn)
GO

sp_RENAME '[MyDatabase].[EmployeesSchema].[Employees].TempColumn', 'EmpId' , 'COLUMN'
GO

It produces :

The constraint 'PK_Employees' is being referenced by table 'Clients', foreign key constraint 'Employees_Clients_FK'.

How can I change the EmpId to auto-increment even though I have the foreign key constraints?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
JAN
  • 21,236
  • 66
  • 181
  • 318
  • 3
    Disable all your FKs before the operation and re-enable them after. See [this question](http://stackoverflow.com/questions/11639868/temporarily-disable-all-foreign-key-constraints) – Code Different Nov 12 '15 at 14:25
  • Drop the FKs then re-add them. – Tab Alleman Nov 12 '15 at 14:53
  • @TabAlleman : This `ALTER TABLE [myTable] NOCHECK CONSTRAINT ALL` doesn't help , the SQL Server doesn't check the contents of the values that I enter in the referenced column, OK , but it still presents the same message . – JAN Nov 12 '15 at 14:59
  • @ZoffDino : This `ALTER TABLE [myTable] NOCHECK CONSTRAINT ALL` doesn't help , the SQL Server doesn't check the contents of the values that I enter in the referenced column, OK , but it still presents the same message – JAN Nov 12 '15 at 15:00
  • Don't know where you got that, but I'm saying `DROP` the FK's and then re-create them. – Tab Alleman Nov 12 '15 at 15:01
  • i'm too lazy to write your code, i'd go as follows: add `on update cascade` to all foreign keys, add the column with the identity, update the old pk column with the new id's (which changes that id in all related tables), drop all foreign keys, the primary key and the old id column, rename the new column, add primary key, add foreign keys. – A ツ Nov 12 '15 at 15:19

1 Answers1

0

If you are fine with retaining all the previous id values you can:

select max(EmpId)
from employees

Then update the employees empid to IsIdentity = Yes and IdentitySeed = "The result of the max id query"

So all new entries after that will have an autogenerated column that starts above the highest EmpId value. This is so any new values do not collide with a previous value.

Dale K
  • 25,246
  • 15
  • 42
  • 71
Ehz
  • 2,027
  • 1
  • 12
  • 11