Can you update a primary key in sql

We use small, stable Identifiers, such as are used to Identify the data in the universe. Don't update the PK! Add a new column is the most. When you then update a value in the PK column in your primary table, the FK references in the other tables will be updated to point at the new value, preserving data integrity.

When you find it necessary to update a primary key value as well as all matching foreign keys, then the entire design needs to be fixed.

It is tricky to cascade all the necessary foreign keys changes. It is a best practice to never update the primary key, and if you find it necessary, you should use a Surrogate Primary Key , which is a key not derived from application data. As a result its value is unrelated to the business logic and never needs to change and should be invisible to the end user. You can then update and display some other column. Don't update the primary key. It could cause a lot of problems for you keeping your data intact, if you have any other tables referencing it.

Stack Overflow for Teams — Collaborate and share knowledge with a private group. Create a free Team What is Teams? Collectives on Stack Overflow. Learn more. How to update primary key Ask Question. Asked 11 years, 9 months ago. Active 1 year, 3 months ago. Viewed k times. Improve this question. Dale K Slavisa Slavisa 3 3 gold badges 11 11 silver badges 26 26 bronze badges. I would argue that, even though it's possible to do what you're asking, it's highly unlikely that it's the right thing to do.

If you get into a situation where you need to change the value of the PK, than you're essentially attempting to change the identity of your data. Usually there's no need to do that and it indicates your data model is probably wrong. If you really must do it, then clone your original data and, if needed, remove the original.

That operation will undoubtedly indicate what your intention is. Add a comment. Active Oldest Votes. But, if you really need to, you can do the following: Disable enforcing FK constraints temporarily e.

Improve this answer. I dont like the idea to bring new coloumn in, so this is what I was looking for. Thaks everybody — Slavisa. Not only will you will disable your FKs while other users change data, but this will support a bad design.

In the Delete Object dialog box, verify the correct key is specified and select OK. In Object Explorer, right-click the table with the primary key, and select Design. In the table grid, right-click the row with the primary key and choose Remove Primary Key to toggle the setting from on to off. To undo this action, close the table without saving the changes.

Deleting a primary key cannot be undone without losing all other changes made to the table. Copy and paste the following example into the query window and select Execute. The example first identifies the name of the primary key constraint and then deletes the constraint. If this option is not selected, a unique nonclustered index is created. Expand the Fill Specification category and type an integer from 0 to in the Fill factor box.

For more information about fill factors and their uses, see Specify Fill Factor for an Index. Select Columns , and then click the ellipses In the Index Columns dialog box, remove the columns from the primary key.

Then add the columns back in the order you want. Friday, June 15, AM. Dear HuaMin Chen Thanks for your suggestion. This will only update the primary key it will not reflect on the foreign keys values.. This is the way. Dear Steven Wang - Shangzhou Thanks for your response but, I have already created the table and foreign key and even have loads of data in the same.