Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.
Sign up to join this community
What's you opinion about Update a primary Key in SQL?
I have a "link" table with 3 key from 3 other tables. Should the administration let people to edit one of the keys?
I usely said no, dont change PK. Add a new line and if you want remove the other line.
sample:
Table A : idA Int
Table B : idB Int
Table C : idC Int
Table Link : PK { idA, idB, idC }
UPDATE [Link] SET idA=@NewIdA
Once a record is created with a primary key, you really shouldn't be updating the primary key. The point of a PK is that it uniquely identifies that row. That's why you should be careful when creating a PK with a composite key (multiple fields that comprise the PK), because if those fields need to change, then you're changing your PK and any depending records need to change as well.
Now in a table that has a Foreign Key to another table, it's ok to update the FK if/when necessary.
So really, when the data needs to change, create the PK on an auto-increment field and let people modify the rest of the data, or delete the entire record when necessary.
However, I don't think there's much difference between "adding a new line and removing the old line", and just modifying the row itself. All of the concerns that others have brought up are still valid.
By "let people to edit one of the keys", I assume you're talking about end users. If that's the case and you don't like having hair, go right ahead. You'll pull your hair out trying to figure out why none of the references work.
OK, I'll be serious now. My philosophy about keys is simple. Never use real data for a key. Period. Sure there are unique data that can be used for a PK, but I NEVER use it for the PK. I also avoid composite keys like the plague. The only time I'll make an exception is if I'm doing some kind of embedded work and I'm limited to a couple of MB of space. Ask yourself: in a reference table, how much space am I going to save by using something other than an incrementing integer as the PK?
That said, conventional wisdom also tells us that we find records to update by the primary key (most of the time). If you change the PK, how do you know you're updating the right record? If I were you, I would avoid ANY user updating the PK. Ever.
Changing a primary key may have grave implications to the integrity of your data.. changing sex from “M” to “F” after other parts of your systems have established relationships to the “M” row may go unnoticed. Hiding the key(s) somewhere in the row may sound good but all you have done is created a way to circumvent data integrity provided by the DBMS. I suppose you could write your own code to keep it straight but now your users are dependent on you instead of rock-solid database principles. I’m not a philosopher so maybe I’m missing your point.
