From a mathematical view, granted that a table has at most one primary key, it seems to be a shortsighted design decision to refer to primary keys by some arbitrary name instead of a simple table property.

As consequence to change a primary key from nonclustered to clustered or vice versa, you have first to search for its name, than drop it and finally readd it.

Is there some advantage in using arbitrary names that I do not see or are there DBMS not using arbitrary names for primary keys?

Edit 2011-02-22 (02/22/2011 for those who don't want to sort there date):

Let my show the function, with which you can derive the names of a primary key from its tables name (using early sql-sever aka sybase system tables):

create function dbo.get_pk (@tablename sysname)
returns sysname
    return (select k.name
    from sysobjects o 
        join sysobjects k   on k.parent_obj = o.id 
    where o.name = @tablename
    and o.type = 'U'
    and k.type = 'k')

As gbn states no people really like the generated name, when you do not supply an explicit name:

create table example_table (
    id int primary key

select dbo.get_pk('example_table')  

I just got


But why must names in sysobjects be unique. It would be perfectly OK to use exactly the same name for the table and its primary key.

Doing it that way, we didn't need to set up naming conventions, which could be accidentally violated.

Now answers to Marian:

  1. I only used the task of changing a clustered into a nonclustered primary key as an example where I need to know the actual name of the pk to be able to drop it.
  2. Things don't need to have proper names, it is sufficient if they can easily uniquely denoted. That is the basis of abstraction. Object orientated programming goes this way. You need not use different names for similar properties of different classes.
  3. It is arbitrary, because it is a property of a table. The name of the table is all you need to know if you want to use it.

5 Answers 5


Primary keys (and other unique constraints) are implemented as indexes, and are dealt with in exactly the same way - it doesn't make sense from the programmer's point of view to have separate code paths for PKs and indexes (it would double up the potential for bugs).

Other than being referred to by foreign keys, a PK is just a unique constraint which is in turn implemented as an index, so changing the properties of a PK is just the same as changing the properties of any other index. Also having an explicit name means they can be referred to in query hints like any other index.

  • 1
    Yeah, also it means which column is used as the PK can be changed. So for books, you might use the ISBN as the PK (which you would want to call ISBN so that it's clear), but then halfway through the development you might decide that you could stock second hand books as well, meaning you'll want a separate record (example off the top of my head). So you'll need to change the PK field to some unique ID.
    – Nathan
    Commented Feb 21, 2011 at 16:16
  • 1
    I'd prefer the phrasing: "a PK is a constraint that uses an index". Sometimes two constraints (a PK and an FK - or 2 FKs) can use the same index. Commented Apr 18, 2012 at 21:00
  • @ypercube I prefer "a PK is a constraint that uses an index in 99.99% of real-world databases", which of course means that it is possible to have a PK constraint without an index, yet no one planning to use that database in the real-world would ever implement it due to performance reasons.
    – Pacerier
    Commented Jul 6, 2012 at 8:28

Not sure I fully understand the question.

If you had an index on a table and you wanted/needed to change the index, would you not also need to change it according to it's name? I suppose you could look up the objectID for the index and do the update that way, but names tend to help people to logically understand what object they are working with.

So, perhaps the answer is that if you have a strong naming convention in use (i.e., name_PK for a Primary Key), then you would be able to easily identify that you were working with the primary key of the table.

I suppose the same thing could also be said for defining FK relationships. I think the use of names is done for logical interpretation, as the objects themselves all have distinct objectIDs.


I would say it's an implementation detail for human readability.

Constraint names are optional (in SQL Server at least), but I'd like to have PK_MyTable for MyTable rather than PK_MyTabl___16feb6d8a


Historically, Primary Keys and Unique Keys are all called Candidate Keys as taught by Christopher J. Date.

A Candidate Key is an index that is Unique and can play the roll of a Primary Key. Thus, all Candidate Keys are Unique Keys. A Primary Key is simply the designation of one of the Candidate Keys as the preferred way to access table data.

In light of this, the name PRIMARY KEY is the arbitrary name of the preferred candidate key attached as a table property. There can only be one Primary Key.

In reality, creating named Candidate Keys only should suffice.

To change an index from clustered to nonclustered and back would just be an exercise of finding the PRIMARY KEY, which is at most 1 by definition. I guess you can say that having a PRIMARY KEY definition is essentially a sense of nostalgia for database purists. That sense of database purity should have called unique keys by the keywords CANDIDATE KEY instead of the actual words UNIQUE KEY.

Click here to see the Definition of a Candidate Key and C.J.Date's comments on it


There's nothing to say that the primary key will represent the table object. The table object is the clustered index, not the primary key. There is nothing that says that the primary key has to be the same as the clustered index. Often it is, but it doesn't have to be. The primary key can easily enough be enforced by a non-clustered index.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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