Why do primary keys have names of their own

primary-key

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.

Best Answer

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.