Re: Indexes and primary keys, from Delaney

From: Uri Dimant (urid_at_iscar.co.il)
Date: 03/04/04 

Date: Thu, 4 Mar 2004 07:31:39 +0200

DW
> Is a unique constraint as efficient (or more) than having an unneccesary
> index?
BOL says:
a.. UNIQUE constraints enforce the uniqueness of the values in a set of
columns.
No two rows in the table are allowed to have the same values for the columns
in a UNIQUE constraint. Primary keys also enforce uniqueness, but primary
keys do not allow null values. A UNIQUE constraint is preferred over a
unique index.

If uniqueness must be enforced to ensure data integrity, create a UNIQUE or
PRIMARY KEY constraint on the column rather than a unique index

There are no significant differences between creating a UNIQUE constraint
and creating a unique index manually. Data validation occurs in the same
manner and the query optimizer does not differentiate between a unique index
created by a constraint or created manually. A unique index and a UNIQUE
constraint cannot be created if there duplicate key values exist.

"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 great idea to define a Primary Key for each table, ... SQL Server does that by automatically ... It is meaningless to say that you want a Unique Constraint (or PK ... > and account number, ...
    (microsoft.public.sqlserver.programming)
  • Re: Help with indexes on a query
    ... All tables in a relational model should have a primary key. ... since you didn't declare a PRIMARY KEY or UNIQUE constraint ... who should know the data), I guess that the combination of VorderingId, ... defining a primary key or unique constraint always ...
    (microsoft.public.sqlserver.server)
  • Re: Choosing Primary Key
    ... Aaron Prohaska wrote: ... > unique constraint and then a identity column as the primary key. ... > five columns that make up the unique constraint are ManufacturerID, ... I think your solution of using an IDENTITY column as the PK is the best ...
    (microsoft.public.sqlserver.programming)
  • Re: auto number
    ... "ABRAHAM GOLDSTEIN" wrote: ... concerned about the primary key number i hope i understood ... If you are concerned about duplicates then put a unique constraint on ... the Autonumber column; if you think you already have a unique ...
    (microsoft.public.access.tablesdbdesign)
  • Re: Table with one row
    ... > Since the table only has one row, we purposely join to it without a ... A simple way to avoid more than one row is to add a dummy column with a ... unique constraint combined with a check clause. ... > Second, to be relationally correct, the table needs a primary key. ...
    (comp.databases.theory)