Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

Question: Is there value in denoting a key as "Primary"?

This question is not about the definition of a primary key. It is not about the fact primary's aren't nullable. It is not about implementations such as MySQL which automatically perform clustered index voodoo behind the scenes on primary keys.

NOTE: The question is not about the benefits of auto-number-ID surrogates over natural keys.

Suppose a table has 3 candidate keys. All non-nullable. All immutable. I find no value denoting a key as "secondary" or "tertiary". I don't find the concept of precedence useful when dealing with multiple keys. I find the same logic against "secondary" and "tertiary" flags applies to the "primary" flag.

I even feel the word "candidate" is not useful for a key. It implies the candidate is not as important as the key that won the primary election. I don't feel precedence has any place in describing keys. The attributes of the key itself (unique, non-nullable, etc) should be sufficient.

Would the relational model be missing anything if we did not have the "primary" attribute to describe 1 of a table's keys?

share|improve this question
4  
You are missing one very important characteristic of a primary key: it is guaranteed to be unique. That might not be the case with other immutable, non-nullable fields. –  Bart van Ingen Schenau Jul 9 '13 at 17:30
2  
I'm not missing the uniqueness attribute. I mentioned it in the 2cd sentance. A regular key is perfectly capable of being non-nullable. –  mike30 Jul 9 '13 at 17:51
3  
If you mentioned uniqueness, I don't see it. Uniqueness and non-nullable are two distinct properties. If you take a person's name, it is reasonable to expect it to be non-null (every person has a name), but not that it is unique (two people having the same name is not that uncommon). –  Bart van Ingen Schenau Jul 9 '13 at 18:01
2  
Sorry. My brain is not working today. I incorrectly interpreted "Unique" as "not null". It is implied that keys are unique. –  mike30 Jul 9 '13 at 18:06
    
"It is not about implementations such as MySQL which automatically perform clustered index voodoo behind the scenes on primary keys" - FYI that is actually a feature specific to InnoDB(/XtraDB), not MySQL in general. –  BlueRaja - Danny Pflughoeft Jul 9 '13 at 21:01
show 1 more comment

7 Answers 7

up vote 2 down vote accepted

Logically and practically speaking there are really no "primary" keys in the relational model [1] because all candidate keys can and do have exactly the same status, features and function in the relational model: candidate keys are irreducibly unique, non-nullable identifiers for tuples in a relation.

The practice of singling out any one key as "preferred", "most important" or for any other purpose is a matter of convention and convenience rather than necessity. If a relation has only one candidate key then we might just as well call it a "primary" key by default. If a relation has more than one key then the choice of which to call "primary" is only as important as you want it to be and the significance of that choice is defined by the user and/or by the syntax and DBMS features used to define it.

You are certainly not alone in finding the idea of designating a "primary" key of somewhat limited and dubious value. Especially so in the database systems of today, compared to those of decades past. The "primary key assumption" is a controversial notion that is arguably outdated and by rights probably ought to be retired by now. Unfortunately it's an assumption that is heavily entrenched in the mindset and practices of many database practitioners.

[1] E.F.Codd originally used the term "primary key" to mean any and all keys of a relation and stated that a relation could have more than one such key. It was only later that the term "candidate key" appeared in the relational database literature and the term "primary key" took on its modern meaning of denoting one and only one key per relation.

share|improve this answer
    
Changed this to the accepted answer. The reference to Codd's concept of primary gives your answer the most weight. –  mike30 Jul 15 '13 at 14:49
add comment

As a matter of general principle, I summarily add an "autonumber" field to every table I create in a relational database, give it a sensible name, and mark it as the Primary Key. Doing this eliminates all sorts of intricacies, like the "candidate, "secondary" and "tertiary" concepts.

Primary keys are well-understood to have certain characteristics: they are unique, and not nullable, among other things. Marking a key a Primary Key makes it very clear to other developers what its purpose is, and what it can be effectively used for.

Mostly, it is guaranteed to be a stable, unique identifier for each record, making it possible for the key to participate in joins with foreign tables. I think I'd pull my hair out if I routinely had to deal with tables containing composite, tertiary, secondary keys, and had to figure out each time how to get a unique identifier so that I could join it in a query.

share|improve this answer
6  
Marking a key as the Primary Key tells developers what they can do with it. That benefit alone is sufficient reason to do it. –  Robert Harvey Jul 9 '13 at 17:15
3  
+100, I'm with you on always including such an id column in every table. –  GrandmasterB Jul 9 '13 at 18:01
4  
"All-surrogate" policy is wrong. Some tables will have surrogate keys, some will have natural keys. But "all-surrogate" is as wrong as "all-natural". –  user61852 Jul 9 '13 at 18:06
5  
Even if the key changes, say, twenty years from now, you can easily cascade update in most modern RDBMSs. -- Which is unnecessary in a system that uses nothing but surrogate primary keys. Why add the complexity of doing things two different ways in the same system, for no appreciable benefit? Interoperability has little to do with providing a surrogate primary key; just send the natural key to the other system instead. –  Robert Harvey Jul 9 '13 at 18:21
2  
While a autoinc number primary key is generally useful for most tables, I've found one case where I don't see any need to use them, and that's for "glue tables" that define a many-to-many relationship. In that case, the primary key is the combination of the FK to the first table and the FK to the second table. –  Mason Wheeler Jul 9 '13 at 18:23
show 9 more comments

If a table has three candidate keys, all unique, non-nullable and immutable, the best choice for a primary key is the smallest candidate key (both in terms of number of fields and field size) that is of sufficient size that you will never run out of possible values to store in it. Ideally, a table's primary key should be one field (which, coupled with the data independence and immutability advantages, make autonumber fields a pretty easy choice for many DBAs, with GUIDs a close second).

The primary key has special conceptual significance over any other candidate key, because it is the one that is used to reference that record as a foreign key on any other table. Every RDBMS I can think of enforces this; you can't specify a unique index as the foreign key; it has to be the primary key. Foreign keys, in turn, enforce referential integrity; the value stored as the foreign key reference in a record of another table must exist as the primary key of the referenced table, meaning you can't save a record referencing a nonexistent PK value, and you can't delete a record whose PK value is referenced by another record as a foreign key.

Now, you might say you can join tables on any field you like, including a unique index for another candidate key. That's correct. You could also say that you could define constraints requiring the value to exist in a query of the other table, and triggers to prevent deletion of a record referenced by another table. That's also correct. My question to you would be, "WHY?". Foreign keys exist to provide all this functionality; why reinvent the wheel, especially when it requires so much work to do so?

Leading back into things you didn't want to talk about, but that are significant when making keying decisions, virtually all RDBMSes (not just MySQL) define the primary key as an index, which determines how the data for the table is structured in the underlying data files. In MSSQL, that's a "clustered" index which means that records are arranged and grouped within the filesystem according to their PK, in addition to the PK appearing in search trees. Not all RDBMSes arrange their data in the same way, but as sequential data access is often important for performance, proper indexing is key to working with real-world RDBMS implementations in an efficient way.

share|improve this answer
    
Accepting as the answer for the comment about primary as a convention to use for foreign keys. Although I agree with it as a convention, I've never seen any RDBMS enforce the use of the PK as the referenced key in a foriegn key. Which database enforces that? Not SQL Server. –  mike30 Jul 10 '13 at 12:45
1  
Just a note, the automatic clustered index on a PK is a very bad idea. For example SQL Servers GUI interface will make a clustered index even if you choose a non-sequential value for the PK. –  mike30 Jul 10 '13 at 12:50
add comment

Technically speaking, a primary key is not different from any other unique non-nullable key: you can get the exact same schema restrictions by just making your columns non-nullable (and even that isn't really a requirement IIRC), and defining a unique key for them.

However, declaring a key as PRIMARY signals that you intend this particular key to be the default way of referencing rows in this table. This marker is important for other people working with your schema, because it tells them that they can expect this key to stay intact, and that you will check any foreign keys in other tables that reference it before changing it. But it is also of interest to the database engine, if only because this extra information can be used for query optimization and caching heuristics.

share|improve this answer
add comment

A "Primary" index is the one that uniquely and canonically identifies a record. If a table were employees, we might have keys on their name, ssn, and hire date, but it would be the employee id that canonically identifies them, and would be the table's primary key.

Without a primary index, you don't have a formal designator of the proper way for another table to refer to a record in your table. So, to extend my example above, you might have payroll indexed by ssn and evaluations indexed by hire date, requiring the actual employee table to be referenced if you wanted to query evaluations against payroll.

share|improve this answer
    
I agree on the usage of a surrogate keys (employee id). But are you saying "primary" and "surrogate" are one and the same? Could we not get the full benefit of surrogates with a simple uniuqe-non-nullable key? –  mike30 Jul 9 '13 at 17:22
    
@mike30: If you have a immutable, always present data value that uniquely identifies each entity, then there is nothing wrong with using that a key. A social security number might work, but often the problem is that there is no data value that can qualify as a primary key (not guaranteed unique, not immutable) or you need a combination of values that makes it inconvenient to reference as foreign keys. –  Bart van Ingen Schenau Jul 9 '13 at 17:39
    
@BartvanIngenSchenau. I agree with you on the use of surrogate keys. But "primary" and "surrogate" are two completely different topics despite the fact that surrogates are often a primary key. –  mike30 Jul 9 '13 at 17:57
    
@mike30 Primary keys can be artificial (surrogate) or natural (business). Surrogateness does not automatically make a column the PK. –  user61852 Jul 9 '13 at 18:11
    
@user61852. Absolutely agree. –  mike30 Jul 9 '13 at 18:17
show 2 more comments

A database needs a way to uniquely identify every record in every table. It needs only one way even if several possibilities exist. But that is used for indexing the table and for making sure action queries correctly affect only the record(s) you want them to affect. Because it needs to know which to use, you should define one of the possible keys as the primary. It doesn't mean the others are less important or that they are not also unique and non-nullable. The others can and should be uniquely indexed and they can also participate in joins to related tables if need be. But one and only one thing must be what the database uses to uniquely identify the record because this key will get added to all indexes (At least in SQL Server). This should be the least mutable of the candidate keys in general. Updating primary keys (espcially of they are also the FKs in related tables) can be an expensive task. It also makes the relationships to other tables easier to define consistently. If you have ever had the displeasure of working with a database where there was no consistent key to use for joins you would understnd how imporatnt this type of consistency is.

share|improve this answer
1  
"this key will get added to all indexes". Do you mean how SQL server by default creates a clustered index on the PK field? –  mike30 Jul 9 '13 at 18:58
    
The question is about keys but this answer appears to be about indexes. The assertion that a database needs "only one way" to identify tuples is incorrect with reference to keys because tables can and often do have multiple keys all of which uniquely identify tuples. –  sqlvogel Jul 14 '13 at 13:24
add comment

TLDR What is the value of a Primary Key annotation? The primary key denotes the quickest means of locating a record. As you mentioned in your question, most SQL implementations use the primary key to denote where in the table file the record is stored. Using a auto-incrementing numeric key avoids unnecessary fragmentation of the file. (Reducing the file size and write time)

As a rule of thumb, a primary key should have no significance to the business. I.E. an SSN should not be used as a primary key. The reason for this is that business rules change. A value that is "always" unique like an SSN usually has an exception that is discovered down the road there are only a Billion possible combinations of 9 digits there are 300 million citizens in America.

Likewise values that will "always" be present have exceptions too. our HR System has to support companies outside of America where employees don't have a Social Security Number

The purpose of a primary key is to give a unchanging identifier to an entity so that I always know where to find that entity. Non-primary keys or Indexes are used to enable a user to easily lookup an entity given knowledge of that attribute.

Think of the primary key as an address to a block of memory. If another entity holds that address, we expect to be able to jump to that address and find the original entity.

Edit I don't tend to write answers on here without knowing what I'm talking about. I know the difference between a surrogate and a primary key. If you want a second opinion. Here you go

You have three choices for defining the primary key for most tables: a 'natural' key, a >system generated or 'internal' key, and (for smaller lookup tables) a mnemonic code.

Internal ID numbers

Rule of thumb: for any table over 100 records, use a system-generated number as the primary key.

Any time your system stores a record, particularly when the record's 'natural' key ID number is controlled by an outside source, there is a risk that the outside source may do odd things with its data - it may re-use the ID number, it may update a record by changing the ID, or the system may have a problem talking to the outside source and may get the same record sent twice. In every such case, your system must not be tightly coupled to the outside source - where "tightly coupled" means that a problem in the outside source system causes your system to fail to work correctly.

Therefore, as a form of humility, your system should accept at face value whatever the outside sources send. The system should not try to fix other systems, nor modify their data, nor maintain referential integrity for them. (Your system should do all of those things for its own internally created data, but not for other systems' data that is imported.)

When other systems do make corrections to earlier errors, your system should take the later data at face value and overwrite any earlier records they sent. In every case, your system should faithfully and accurately record whatever is sent from other systems.

This approach can only be made to work with system generated ID numbers. Other keys could be created instead, such as the external system's primary key plus (say) the date, but this merely tries to obtain the benefits of humility without acknowledging the work, and exposes your system to problems if any of the above mentioned problems were to occur within the course of a single day (when the dates would be duplicated).

(On an earlier system I tried the external-key-plus-datetime approach, only to find that duplicates arrived within the same datetime down to the second -- one second being the finest granularity of an Oracle datetime.)

Note: this does NOT mean your system allows users to enter bad data. Data that users enter directly in the system is part of the system's own data, and therefore it takes responsibility for that data's cleanliness and accuracy. All data entry screens will provide and enforce "edits" to ensure the correctness of data entered by users directly into your system.

Natural Keys

Rule of thumb: don't use natural keys as primary keys; instead, store them as attributes.

I view 'natural' keys as actually just the keys of an external system -- even if that external system is the outside world. Generally, natural keys are untrustworthy in the extreme. Here are some examples of their untrustworthy behavior:

A criminal steals someone's (US Government generated) Social Security Number (SSN) to commit fraud (now two people have one SSN). Later, the victim is assigned a new SSN (now one person has had two SSNs at different times.) A system that used SSNs as a 'natural' key would have a terrible time with this scenario. A visitor to the US (or an American child) who has no SSN cannot be tracked by the system, and essentially doesn't exist.

A supplier re-uses SKUs (Stock Keeping Units), i.e. when a product becomes obsolete its SKU is reassigned to a new product.

You'll still want to store the supplier SKU -- you just need to be alert to its potential for misbehavior.

Mnemonic Codes

Rule of thumb: for any table over 100 records, use a system-generated number as the primary key.

For lookup tables, I like to take advantage of the fact that primary key values of lookup tables become foreign key values in the tables that reference them -- and if a code is mnemonic (its meaning is easily remembered) then often the user doesn't even need to do a join to know what the key means.

share|improve this answer
    
I think you are describing the concept of "surrogate", not "primary". –  mike30 Jul 9 '13 at 20:58
add comment

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.