Update primary key column in sql

Quick access. Search related threads. Remove From My Forums. Answered by:. Archived Forums. 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. 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. 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. Some day, the next developer to work on this database will be asking here on SO how to fix this PK update mess. This is a great answer for people who need to get this done. It would help if you would put a bold disclaimer on top of your answer that it's a bad decision to do so and a consequence of a bad database design.

Also, it would help if you would enumerate all the issues, associated with that approach, mentioned in these comments, to discourage people from doing it without further considerations. The point is, the Key or Record ID is migrated to wherever a reference is required. Write a proc. Please, just surrogate, there is no such thing as a "surrogate key", because each word contradicts the other.

Either it is a Key made up from the data xor it isn't. A surrogate is not made up from the data, it is explicitly non-data. It has none of the properties of a Key. Notify me of new posts via email. This site uses Akismet to reduce spam. Learn how your comment data is processed.

Following are the steps used for alter and modify the Primary key of existing table: 1. Like this: Like Loading Leave a Reply Cancel reply Enter your comment here You can modify the primary key of a table by changing the column order, index name, clustered option, or fill factor. Skip to main content. This browser is no longer supported.

Download Microsoft Edge More info. Contents Exit focus mode. Is this page helpful?