Re: Indexes and primary keys, from Delaney

From: Kalen Delaney (replies_at_public_newsgroups.com)
Date: 03/04/04 

Date: Thu, 4 Mar 2004 03:12:57 -0800

Hi David

One of the issues here is that the Primary Key is a logical concept, and an
index is a physical concept (an actual structure). During logical design, it
is a great idea to define a Primary Key for each table, so you know you have
a way to uniquely identify each row.

If when you physically build your tables, you use SQL Server's constraint
mechanism to define a set of columns as the PK, you are asking SQL Server to
enforce the uniqueness of the columns. SQL Server does that by automatically
building a unique index. But they are still two different things. You could
just decide you want a column to be your PK without asking for an index to
enforce it. Then YOU would have to find another way to make sure there were
no duplicates.

So you can't just drop the index that was built to support the PK, because
you never created the index separately. You said you want SQL Server's help
to maintain uniqueness, and to comply, it built the index. If you no longer
want the index, you must tell SQL Server you no longer want it to help
maintain uniqueness, and you do that by dropping the PK constraint.

As you have figured out, you cannot have the PK or a Unique constraint
without an index; it is the index that allows SQL Server to enforce the
uniqueness. You can only have a logical PK that is not declared in the
database.

It is meaningless to say that you want a Unique Constraint (or PK
constraint) but that the index is unnecessary. It is the index that allows
SQL Server to guarantee the uniqueness.

You had a lot of questions as you were trying to make sense out of these
concepts. If I haven't answered all of them, just let me know! 

--
HTH
----------------
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"DW" <None> wrote in message news:Xns94A1E1BFB6A19DWalker@207.46.248.16...
> 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
    ... > Is a unique constraint as efficient than having an unneccesary ... PRIMARY KEY constraint on the column rather than a unique index ... > claim that the sequence number is unique and never reused. ... > and account number, ...
    (microsoft.public.sqlserver.programming)
  • Re: Indexes and primary keys, from Delaney
    ... > One of the issues here is that the Primary Key is a logical concept, ... > constraint mechanism to define a set of columns as the PK, ... > asking SQL Server to enforce the uniqueness of the columns. ... >> SSN and account number, ...
    (microsoft.public.sqlserver.programming)
  • Re: Clustered Index question
    ... Columnist, SQL Server Professional ... If I keep the primary key, ... >select packet_time,packet_data from packet where ... >have the clustered index (either on PACKET_TIME or on ...
    (microsoft.public.sqlserver.programming)
  • A pk is *both* a physical and a logical object.
    ... Primary key is a logical constraint. ... but SQL Server does not allow you to create a unique or primary ... constraints) by indexing is purely *platform dependent*. ... PKs are part of the ANSI SQL standard *for db implementations*. ...
    (comp.databases.theory)
  • Re: Re-Seed in SQL Server
    ... Add a new field to the table that holds the Primary Key. ... or something like that and make it an Autonumber field and a Primary Key. ... If the table is already on the SQL Server you could try the below. ... that your Autonumber field in the Access Database has gotten corrupted. ...
    (microsoft.public.access.tablesdbdesign)
Loading