EXPERT RESPONSE
That's a great question.
The main purpose of a primary key is to implement a relationship
between two tables in a relational database; it's not called a relational
database for nothing! More specifically,
the primary key is the
"target" which a foreign key can reference.
You cannot declare a foreign key in table B
to relate to table A unless the primary key in table A
has been defined.
So right away, here's the first conclusion:
if your database has a table which no other
table relates to, then this table doesn't
need a primary key. Nevertheless, the table can still have one,
and it is common practice to declare one anyway.
One reason for declaring a primary key, even if no other table
references it, comes from what we could call a side benefit -- the
primary key gets an index.
To the best of my knowledge, all databases utilize an index in order to
implement the uniqueness constraint that a primary key requires.
The reason is simple: when you tell the database to insert a new row, it
has to check the new row's primary key value against all the existing primary key
values to make sure the new value is not there yet.
There's no faster way do accomplish this than by looking up the value in
an index. You could say this is defensive behaviour
on the part of the database, because it knows that
looking up a value is always faster
using an index than scanning a table.
So by declaring a primary key, you get an index, and chances are,
you'll need it for retrieval anyway. Most applications have at least one query
which is supposed to return a single row from the table.
This is often based on the primary key, because the primary
key is, by definition, capable of distinguishing each
row from all the others. So when searching for a
particular row using the value of the primary key,
the database can uses the same index to speed up the retrieval
that it set up for itself to ensure
that the primary key will always be unique.
Yes, you can declare indexes yourself. In fact,
you should declare an index on every column that needs
one, but which ones need one is a different discussion
for another day. Furthermore, you should
not declare a primary key and also an index
of your own on the primary key. That would be redundant, unless
it's a composite primary key, in which case
you might need additional indexes, at least on columns of
the primary key not in the same order, or on subsets.
Whew! As I said, that's another discussion.
In the meantime, go ahead and declare primary keys
for your tables.
For More Information
|
