Douglass MacLean
asked on
Change integer primary key to IDENTIITY(1,1) in SQL Server 2008 R2 Express
My customer has SQL Server 2008 R2 Express installed. One of the tables has a primary key QueueID int NOT NULL
I need to change it to QueueID int NOT NULL IDENTITY(1,1)
When I try to do that in the table designer and click Save, I get the attached error message.
I tested it on my own SQL Server 2012 (not express) and it works fine.
Is there some setting I need to change on the customer's SQL Server to allow the change? Or is it s hard-stop restriction in the Express edition?
Best Regards,
Doug
I need to change it to QueueID int NOT NULL IDENTITY(1,1)
When I try to do that in the table designer and click Save, I get the attached error message.
I tested it on my own SQL Server 2012 (not express) and it works fine.
Is there some setting I need to change on the customer's SQL Server to allow the change? Or is it s hard-stop restriction in the Express edition?
Best Regards,
Doug
Saving changes is not permitted. The changes you have made require the following tables to be droped and re-created. You have either made the changes to a table that can't be re-created or enabled the option Prevent changes that require the table to be re-created.
Can-t-change-tlkpQueue.PNG
There's already records in the table?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Take care using this approach, suggest you preview the script first
ASKER
Thanks, That's exactly what I needed. And I agree with being careful:
1. 1've already tested it on my development copy of the live database
2. I'm doing the change at night when no one is on the system and making a DB backup before I do it.
(Belt ad suspenders) :-)
1. 1've already tested it on my development copy of the live database
2. I'm doing the change at night when no one is on the system and making a DB backup before I do it.
(Belt ad suspenders) :-)
Great (and thanks!)
All the best, Paul
All the best, Paul