• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 128
  • Last Modified:

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
Can-t-change-tlkpQueue.PNG
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
0
Douglass MacLean
Asked:
Douglass MacLean
  • 3
1 Solution
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
There's already records in the table?
0
 
PortletPaulCommented:
go to:: Tools... Options... Designers... Table and Database Designers

Then untick the "prevent saving changes that require table re-creation".
0
 
PortletPaulCommented:
Take care using this approach, suggest you preview the script first
0
 
Douglass MacLeanCTOAuthor Commented:
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) :-)
0
 
PortletPaulCommented:
Great (and thanks!)

All the best, Paul
0

Featured Post

[Webinar On Demand] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now