Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.
share|improve this question
    
I'm not sure I understand this part "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.". 1) This is available only for SQL Server.. because in Oracle/Mysql..etc you don't have these types of indexes. 2) In all DBMS the database objects have names that can be found in the system catalogs. How would you check a database object if not by a name? 3) How is a PK name arbitrary (as you set it when you create it)? –  Marian Feb 21 '11 at 21:08
    
Oh, so the questions are theoretical and philosophical :-). Way above my skill set. Side question, how would you practically work with unnamed objects in a database? In db you also have administrative work, not only programming.. So need to script objects, see differences, update..etc. –  Marian Feb 22 '11 at 12:47
    
@Marian I was speaking about primary keys. Did I propose to describe Indexes by tablename and column lists? –  bernd_k Feb 22 '11 at 13:10

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.

share|improve this answer
    
+1 Great explanation. –  Matt M Feb 21 '11 at 13:20
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 MacInnes Feb 21 '11 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. –  ypercube Apr 18 '12 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 Jul 6 '12 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.

share|improve this answer
    
Rule #1 in grown databases: never believe in consistent use of naming conventions. –  bernd_k Feb 20 '11 at 17:45

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

share|improve this answer

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

share|improve this answer

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.

share|improve this answer

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.