3

I am trying to change a primary key Id to identity to increment 1 on each entry. But the column has been referenced already by other tables. Is there any way to set primary key to auto increment without dropping the foreign keys from other tables?

Andomar
  • 232,371
  • 49
  • 380
  • 404
akd
  • 6,538
  • 16
  • 70
  • 112
  • 1
    You cannot alter the identity property, only set it for new columns. Create a new identity column, copy the values with `set identity_insert [table] on`, and drop and re-create the foreign key constraints. – Andomar Feb 26 '15 at 11:00
  • Alternatively, from SQL Server 2012 onwards, you can use a sequence to supply a default value, which will not require dropping the column. See http://stackoverflow.com/questions/14413383/sequence-as-default-value-for-a-column. Take note that in that solution, the value can be modified, which can be good or bad depending on your scenario. – Jeroen Mostert Feb 26 '15 at 11:26
  • If you are using sql server 2012 or above, I would suggest you to create a sequence and add a default value for the id column referencing the sequence. It would behave similary to identity, but you don't need to recreate the column. – Jesús López Feb 26 '15 at 11:27

1 Answers1

0

If the table isn't that large generate script to create an identical table but change the schema it created to:

CREATE TABLE MYTABLE_NEW (
PK INT PRIMARY KEY IDENTITY(1,1),
COL1 TYPEx,
COL2 TYPEx,
COLn
...)
  1. Set your database to single-user mode or make sure no one is in the database or tables you're changing or change the table you need to change to READ/ONLY.
  2. Import your data into MYTABLE_NEW from MYTABLE using set IDENTITY_INSERT on
  3. Script your foreign key constraints and save them--in case you need to back out of your change later and/or re-implement them.
  4. Drop all the constraints from MYTABLE
  5. Rename MYTABLE to MYTABLE_SAV
  6. Rename MYTABLE_NEW to MYTABLE
  7. Run constraint scripts to re-implement constraints on MYTABLE

p.s. you did ask if there was a way to not drop the foreign key constraints. Here's something to try on your test system. on Step 4 run ALTER TABLE MYTABLE NOCHECK CONSTRAINT ALL and on Step 7 ALTER TABLE MYTABLE CHECK CONSTRAINT ALL. I've not tried this myself -- interesting to see if this would actually work on renamed tables.

You can script all this ahead of time on a test SQL Server or even a copy of the database staged on a production server--to make implementation day a no-brainer and gauge your SLAs for any change control procedures for your company.

You can do a similar methodology by deleting the primary key and re-adding it back, but you'll need to have the same data inserted in the new column before you delete the old column. So you'll be deleting and inserting schema and inserting primary key data with this approach. I like to avoid touching a production table if at all possible and having MYTABLE_SAV around in case "anything" unexpected occurs is a comfort to me personally--as I can tell management "the production data was not touched". But some tables are simply too large for this approach to be worthwhile and, also, tastes and methodologies differ largely from DBA to DBA.

Sting
  • 333
  • 2
  • 11