Why do primary keys have names of their own?

Question

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
as
begin
    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')
end
go

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

PK__example___3213E83F527E2E1D

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.

Answer 1

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.

Answer 2

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.

Answer 3

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

Answer 4

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

Answer 5

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.