Can't set primary key without dropping and re-creating table

Paul Kraemer 266 Reputation points
2023-03-23T14:36:35.6533333+00:00

Hi,

I am using SQL Server Standard (64-bit), version 11.0.7507.2 (when I go to Help | About, it is shown as SQL Server 2012). I have a table that has just one record. This table has no primary key. I'd like to make one column the Primary Key. In the single record in this table, this column has a value that is not Null. In SQL Server Management Studio, I opened the table in Design View. I right-clicked on the column I want to make the Primary Key, and chose "Set Primary Key". When I try to save my modified table design, I get the following message:

Saving changes is not permitted. The changes you have made require the following tables to be dropped and re-created. You have either made changes to a table that can't be recreated or enabled the option Prevent saving changes that require the table to be re-created.

Currently, there are people using a Microsoft Access Front-end application that has linked tables that are connected to tables in the SQL Server Database (including the table for which I'd like to set a Primary Key). If possible, I'd like to set the Primary Key in a way that would (1) not require me to force all users to close the client application and (2) not require me to re-enter all the data that is stored in the one record of this table.

This table stores "Preferences" that affect how the application works, but that are not changed regularly. I have no concern that data in the table will be in the process of being changed while I am attempting to set the Primary Key. The only reason I want to set the Primary Key is because I suspect the fact that there is currently no Primary Key is resulting in the linked table in the Access front end application not being updateable. (The fact that this table is currently not updateable from the front end application is another reason why I am certain that none of the active client connections will be making changes to this data.)

Any advice as to the best way I can go about making this change will be greatly appreciated.

Thanks in advance,

Paul

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,601 questions
Accepted answer
  1. Viorel 111.5K Reputation points
    2023-03-24T02:00:03.68+00:00

    I think that you should try unchecking the “Prevent saving changes that require table re-creation” option from Options dialog, Tools menu of Management Studio, if there are no possible negative effects.

    See also: https://learn.microsoft.com/en-us/troubleshoot/sql/ssms/error-when-you-save-table.

    1 person found this answer helpful.
    0 comments

1 additional answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Erland Sommarskog 100.8K Reputation points MVP
    2023-03-23T21:47:06.6+00:00

    While you got help from removing that check, the check is there for a reason. The table designer is rotten from the inside and out, and all sorts of bad things can happen if you run which check disabled.

    The correct solution is simple:

    ALTER TABLE MyTable ADD CONSTRAINT pk_MyTable PRIMARY KEY (keycol)
    1 person found this answer helpful.