Design Question - Primary Keys

Hello All,
I am in the midst of designing a database, and I have a very general
(pseudo-newbie) question.
Should all tables have a Primary Key defined?
As I understand it, there are two reasons to have a primary key:
- It is used in conjunction with a Foreign Key on a table, and
- It automatically creates an Index on a table
I would guess that most tables fit one or both of the criteria listed above,
but is it good practice for all tables, or is it unneeded overhead? For
instance, I will have a few tables that are small (maybe 50 rows, max) and
generally fixed (the data is permanent, or nearly permanent, and won't
change), and probably won't be accessed in a multi-table query.
Thanks,
pagates"pagates" <pagates@.discussions.microsoft.com> wrote in message
news:5F8D4C00-28F8-4DE9-884D-7300F7469329@.microsoft.com...
> Hello All,
> I am in the midst of designing a database, and I have a very general
> (pseudo-newbie) question.
> Should all tables have a Primary Key defined?
> As I understand it, there are two reasons to have a primary key:
> - It is used in conjunction with a Foreign Key on a table, and
> - It automatically creates an Index on a table
> I would guess that most tables fit one or both of the criteria listed
> above,
> but is it good practice for all tables, or is it unneeded overhead? For
> instance, I will have a few tables that are small (maybe 50 rows, max) and
> generally fixed (the data is permanent, or nearly permanent, and won't
> change), and probably won't be accessed in a multi-table query.
> Thanks,
> pagates
You forgot the most important reason.
...whose values uniquely identify each row in the table and enforces the
entity integrity of the table.
Some would argue that a table without a primary key is not even a table.
Why would you need such a table?|||Hi Raymond,

> You forgot the most important reason.
> ...whose values uniquely identify each row in the table and enforces the
> entity integrity of the table.
> Some would argue that a table without a primary key is not even a table.
> Why would you need such a table?
Good point. I guess I was thinking of the case of a small table (2 or 3
columns) of relatively small size and constant data, and where the
combination of data on all columns comprises the uniqueness of the table.
For instance, a table that consists of "header information": maybe a company
name, and an "EstablishedIn" date. Maybe this is kept for a web site
provider company to generate some header information on a page for its
customers. Assuming that this is a small company that has only a few
customers (and plans to stay this way), this is a small table with constant
data, and the combo of CompanyName and EstablishedIn date will always be
unique.
(OK, perhaps not the best example, but the first thing that comes to mind.)
Mind you, it was my "gut feeling" that tables should always define primary
keys, but I didn't know if there was a reason not to (for instance,
overhead), especially if it was known that all rows are unique, and that the
uniqueness is the combination of all columns of a table.
Thanks,
pagates
"Raymond D'Anjou" wrote:

> "pagates" <pagates@.discussions.microsoft.com> wrote in message
> news:5F8D4C00-28F8-4DE9-884D-7300F7469329@.microsoft.com...
> You forgot the most important reason.
> ...whose values uniquely identify each row in the table and enforces the
> entity integrity of the table.
> Some would argue that a table without a primary key is not even a table.
> Why would you need such a table?
>
>|||pagates wrote:
> Hello All,
> I am in the midst of designing a database, and I have a very general
> (pseudo-newbie) question.
> Should all tables have a Primary Key defined?
> As I understand it, there are two reasons to have a primary key:
> - It is used in conjunction with a Foreign Key on a table, and
> - It automatically creates an Index on a table
> I would guess that most tables fit one or both of the criteria listed abov
e,
> but is it good practice for all tables, or is it unneeded overhead? For
> instance, I will have a few tables that are small (maybe 50 rows, max) and
> generally fixed (the data is permanent, or nearly permanent, and won't
> change), and probably won't be accessed in a multi-table query.
> Thanks,
> pagates
If the data in your small tables is truly fixed and unchanging then
there is no reason NOT to add a primary key. Keys = integrity. One day
you WILL update those tables and how many things will break if you get
duplicates in there because you didn't bother to declare the key?
Another reason: keys are useful metadata for those who will have to
support and develop your system in future and for those who will
utilise the data. Leaving out keys tells them only that you didn't know
or didn't care what infromation the table was supposed to represent. If
you are learning then start learning good habits now.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||> Mind you, it was my "gut feeling" that tables should always define primary
> keys, but I didn't know if there was a reason not to (for instance,
> overhead), especially if it was known that all rows are unique, and that
> the
> uniqueness is the combination of all columns of a table.
Once you decide what information you need to store in a database, as a
database designer your focus should change to how to keep garbage out. It
doesn't matter how the database performs if the answers it returns are
incorrect. Integrity constraints are extremely important. Murphy's Law
applies to databases, too: if it can happen, it will. You must make sure
that it can't. The only way to do this is to apply constraints to the
database, but that's not enough. You should also normalize and
orthoganalize the database to minimize, or if possible, to eliminate
redundancy. Redundancy cedes the database's constraint checking
responsibility to the application, so it's extremely important to minimize
it. You should also be careful with your queries: many use WITH(NOLOCK) to
improve performance, but as I've posted several times on this forum, NOLOCK
will make your queries return incorrect results at lightning speed. If
performance is a problem, add hardware, add indexes--add both, but don't
compromise integrity.
Whenever I come across a database without constraints, I have to spend days,
if not ws, cleaning out the garbage. A poorly designed database can
double or even triple the amount of time needed to develop an application,
and even then, because there's no way to be sure that the database doesn't
contain garbage, it's much, much more difficult to test and troubleshoot
application code. A poor foundation can turn a two-month project into a
two-year project.
"pagates" <pagates@.discussions.microsoft.com> wrote in message
news:67F5B67A-629E-45E0-A130-DB0582754000@.microsoft.com...
> Hi Raymond,
>
> Good point. I guess I was thinking of the case of a small table (2 or 3
> columns) of relatively small size and constant data, and where the
> combination of data on all columns comprises the uniqueness of the table.
> For instance, a table that consists of "header information": maybe a
> company
> name, and an "EstablishedIn" date. Maybe this is kept for a web site
> provider company to generate some header information on a page for its
> customers. Assuming that this is a small company that has only a few
> customers (and plans to stay this way), this is a small table with
> constant
> data, and the combo of CompanyName and EstablishedIn date will always be
> unique.
> (OK, perhaps not the best example, but the first thing that comes to
> mind.)
> Mind you, it was my "gut feeling" that tables should always define primary
> keys, but I didn't know if there was a reason not to (for instance,
> overhead), especially if it was known that all rows are unique, and that
> the
> uniqueness is the combination of all columns of a table.
> Thanks,
> pagates
> "Raymond D'Anjou" wrote:
>|||I see no reason why any table would not have a unique key, even if it's just
the datetime that the row was inserted.
The real debate is whether or not natural keys or an additional integer
based surrogate key (ex: identity column) should enfore foreign key
relationships.
"pagates" <pagates@.discussions.microsoft.com> wrote in message
news:5F8D4C00-28F8-4DE9-884D-7300F7469329@.microsoft.com...
> Hello All,
> I am in the midst of designing a database, and I have a very general
> (pseudo-newbie) question.
> Should all tables have a Primary Key defined?
> As I understand it, there are two reasons to have a primary key:
> - It is used in conjunction with a Foreign Key on a table, and
> - It automatically creates an Index on a table
> I would guess that most tables fit one or both of the criteria listed
> above,
> but is it good practice for all tables, or is it unneeded overhead? For
> instance, I will have a few tables that are small (maybe 50 rows, max) and
> generally fixed (the data is permanent, or nearly permanent, and won't
> change), and probably won't be accessed in a multi-table query.
> Thanks,
> pagates