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
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:
- 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.
- 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.
- 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.