Indexes and primary keys, from Delaney

From: DW (None)
Date: 03/04/04 

Date: Wed, 03 Mar 2004 21:10:17 -0800

I want to ask a semi-general question, which I think I can do without
giving my whole database definition.

Books and articles about table design say things like "Columns that are
the primary keys or that are unique are most likely to be joined and
frequently queried... When no naturally efficient compact key exists,
it's often useful to manufacture a surrogate key using the identity
property on an int column". (Inside MS SQL Server 2000 by Kalen Delaney
and Ron Soukup, as many of you might recognize, page 272.) Then it says
to use this surrogate key for joining and data retrieval. I see similar
advice frequently.

I understand this........ but I have a database with a big transaction
table. The people who supply the daily updates claim that the "key"
consists of the SSN, account number, transaction date, and a sequence
number. They insist that all four make up the key, although they also
claim that the sequence number is unique and never reused. Okay.

I don't want a wide primary key, because I'll never query on this -- the
sequence number has no meaning and doesn't appear in any other table.

I generally query and/or group on SSN, often on the combination of SSN
and account number (which identifies a unique account), and often query
on transaction date ranges.

I originally had all four fields as a composite primary key. I think
this is a bad idea; the index is too big. The sequence number could be
the primary key, but there's really no need for it be indexed. If I
created a surrogate key just to have a primary key, I would never query
or join using it.

I fond myself querying on the SSN/account number combination, or joining
on SSN and Account Number in the account table, for example, being equal
to the SSN and Account Number in the transactions table. (SSN/Account
Number is the primary key in the account table; that makes sense and
seems normal to me.)

I'm having trouble figuring out how this good advice applies to my
Transactions table. Reams of good advice can't be all wrong.

Even without you guys knowing all the things I do with this data, is it
reasonable cases like this to have a primary key (the transaction
sequence number) that's not indexed? I don't really care if the
sequence number is unique or not, but I could make a Unique constraint.
(I always make a primary key if only so I can open my SQL table from MS
Access, as a project, if I want to.)

Where the book says that you can't drop the index created as a result of
a primary key, but must instead drop the constraint, is kind of
confusing -- dropping the constraint drops the index? I might want to
drop the index and leave the Unique constraint.

Is a unique constraint as efficient (or more) than having an unneccesary
index?

Thanks.

David Walker

Relevant Pages

  • Re: Indexes and primary keys, from Delaney
    ... bloat all nonclustered indexes and the likelihood of page splitting. ... If you have a clustered index on SSN,ACCOUNT,DATE, SEQUENCE ... > and account number, ... > I originally had all four fields as a composite primary key. ...
    (microsoft.public.sqlserver.programming)
  • Re: 2 identical table designs
    ... physical location is logical or down to some "policy" decision. ... If there is a natural primary key (say an index based on both Company Code ... If the combination of Company Code and Account are currently unique in both ...
    (microsoft.public.access.tablesdbdesign)
  • Re: Table Design - Relationships
    ... how I design the forms, I always get errors, e.g. "Forms not updatable" or ... Can a facility have a bill without an account? ... > FacilityID 'Autonumber - Primary Key ...
    (microsoft.public.access.tablesdbdesign)
  • Re: Distinct insert
    ... however at the same time inserting a unique primary key into the same ... Primary Key From Table 1, Account Number ... I need to put that information in a seperate table that is similair to this: ...
    (microsoft.public.sqlserver.mseq)
  • Re: How should I generate a primary key?
    ... wrong with using IDENTITY or SEQUENCE? ... To avoid complications in any relational database, ... value and primary key values should not be changeable for any reason. ... obtain their value outside of the transaction. ...
    (comp.databases)