Tell me more ×
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's 100% free, no registration required.
up vote 4 down vote favorite

I was reading about keys in this DatabaseSystems book -

The primary key should be chosen such that its attributes are never, or very rarely, changed. For instance, the address field of a person should not be part of the primary key, since it is likely to change.

Can someone provide an explanation to this ?

Is it because a change might be a danger to its inherent requirement of being unique ?

I cant really make out the proper logic for this.

share|improve this question
4  
One possible reason. If the PK is being used as a foreign key then updating the PK also means the change has to be propagated in multiple places. 5 desirable criteria are listed here. Not sure what the source of that list is though. – Martin Smith Jul 18 '12 at 9:11

4 Answers

up vote 3 down vote accepted

Keys are identifiers. They permit database users to identify the specific things about which information is being recorded in a database. If the values of key attributes are subject to change then there may be a risk of misidentification, particularly if the identifying information is also recorded elsewhere outside the database (e.g. in other databases, documents or in the mind of the users).

Sensible criteria to consider when choosing what keys to use are: Stability, Simplicity and Familiarity. These are good criteria for choosing any key, not just "primary" keys (the designation of a "primary" key is essentially arbitrary and a matter of convention or convenience - it doesn't mean those keys are fundamentally different to any other).

Stability, simplicity and familiarity are of course all subjective and relative criteria. Any attribute, whether key or not, might need to change and sometimes the ability to change a key attribute is a necessary part of maintaining accurate data and the correct operation of the systems that depend on the data. Very often there is no way to foresee a future requirement that may require such a change. It isn't necessary or even achievable to expect all keys never to change.

Uniqueness isn't really the issue because uniqueness needs to be enforced whether key values change or not. The uniqueness is implicit in the fact that it is a key and is maintained as such by the DBMS.

share|improve this answer
up vote 1 down vote

A relational database would typically contain several related tables. The relationships between tables use what is called a foreign key column which is basically the primary key of a parent table placed in a child table's list of columns (with other rules not listed here). For simplicity, you can say that relationships in a database depend on Primary keys very very much. Now if a value is changed, the system has to perform so many changes to coupe with this change. As an example, say you have a Social Security Number of a person and you keep track about this person's properties, cars, jobs, wive(s), kids and let's say you change the SSN value. The system will have to change the corresponding value in each of those related (child) tables.

This process is not always good to perform on-line.

Another reason is business related. Say you are issuing an EmployeeID Cards, if the number on the card changes, then the card is no longer usable and you have to print another one, not only that, but this means that the salary information has to change, the insurance information has to change as well as other information related to the EmployeeID. In business, sometimes not all this information is integrated or even automated, so this change leads to a lot of work and possible inconsistency of information in manual systems.

Such changes may not only be localized to one organization and may have to affect other external organizations or systems, which would make life very difficult, because you have to ask your business partners to carry such changes on their end too.

Yet, another reason for not changing key values is that when you do so, history tracking becomes difficult. All history logs and documents will not reflect the current value making it difficult to interpret or even find data in manual systems.

share|improve this answer
up vote 0 down vote

Is simple as this: the PK is the identity of the entity. It cannot change. 'Change' of PK from value 'A' to value 'B' means an onld entity with the key 'A' was deleted and a new entity with value 'B' was created. 'B' is not the same entity as 'A'. If you apply this simple concept throughout the data model you save yourself a lot of problems. IT also makes very clear the problem inherent in accepting that objects could change identity (eg. keeping track that 'B' was once 'A', and understanding that past history that meantions 'A' refers to 'B', and not to some other entiry that now has the identity 'A').

Technical details of how PKs are implemented/enforced (eg. uniqueness of index etc) play no role, this requirement is entirely at the logical layer. There is also a side effect that not allowing PKs to ever change makes optimistic locking actually possible. Under a mutable PK is not really possible to implement optimistic locking (at least not orrectly).

share|improve this answer
1  
What is the relevance of saying the identity of an "entity" cannot change? By "entity" I understand you to mean "the thing about which information is recorded in the table". In practice the identifying attributes of things do sometimes change. In any case, the process and effect of changing keys is exactly the same whether you say a thing is the same entity afterwards or not. Enforcing immutability of keys is practically impossible in very many cases and unlikely to be helpful. – sqlvogel Jul 18 '12 at 10:30
1  
@sqlvogel: You are praising natural keys. A myth. Surrogate keys will never have such issue. And will never change for the lifetime of the entity. – Remus Rusanu Jul 18 '12 at 12:27
You can't have read what I wrote. I was not "praising" anything. I've certainly changed surrogate key values plenty of times. In fact I'm surprised if you haven't encountered the need to update surrogate keys. Anyway, the question is about keys in general, not just surrogates. If you meant your answer to apply only to surrogate keys then you ought to say so. – sqlvogel Jul 18 '12 at 12:53
up vote -2 down vote

Most RDBMS like SQL Server, automatically creates clustered index on primary key and keep organise records physically on this primary key. If it will change frequently than SQL Sever have to work alot to keep records physically organized. It is possibly to create clustered index separatly on different column (obviously with less or no changes). In this case your mentioned rule will applied to clustered index key and not the primary key. So, your views about primary key are basically about clustered index and not the primary key. Primary Key should only be unique and not null.

share|improve this answer
1  
Apparently, SQL Server and MySQL/InnoDB does that. Oracle, Sybase, PostgreSQL and MySQL/MyISAM doesn't. Others–don't know. But certainly not most. – dezso Jul 18 '12 at 8:31
And the question is about PRIMARY keys, not clustered indices. – ypercube Jul 18 '12 at 8:31

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.