primary key

ABC

unread,

Mar 10, 2002, 3:53:22 PM3/10/02

to

Is the use of multiple columns as primary key a good design?

There are tables with combination of 2 or more columns create a unique row.
But I found this increase complexity in maintainence.

Thanks

sathya

unread,

Mar 10, 2002, 6:14:28 AM3/10/02

to

Hi,
It is necessary for some cases. If you don't like then you have
to redesign your tables someway. How you found it is increasing the
complexity. Give the example then i will tell u.

regards,
sivababu

"ABC" <nos...@nospam.com> schrieb im Newsbeitrag
news:edE$#AByBHA.1228@tkmsftngp04...

Erland Sommarskog

unread,

Mar 10, 2002, 5:36:25 PM3/10/02

to

ABC (nos...@nospam.com) writes:
> Is the use of multiple columns as primary key a good design?

Yes. I've seen people in the newsgroup posting a set of tables where
each table have had a one-column primary key, which in most cases have
been IDENTITY columns. Now, that is what I call bad table design.

Here is an example from the system I work with. A basic concept is an
account, so there is a table with a one-column key, accno. An account may
have one or more different depots, so there is a table depots, and this
table has a two-column key (accno, depno). In a depot you may 0 or more
positions in various instruments (stocks, bonds etc). For some derivatives,
you keep different counts for bought and sold, so for positions there is
a table with a four-column key (accno, depno, insid, boughtsoldflag).

A position may be custody in several places, and at each such place you
have several accounts. So for this specification you would expect a
six-column key of (accno, depno, insid, boughtsoldflag, custodyplace,
custodyaccount), but in fact we've opted for introducing an artificial
key for the position, so it's only a three-column key (posid, custodyplace,
custodyaccount).

--
Erland Sommarskog, SQL Server MVP
som...@algonet.se
Books Online (updated!) for SQL 2000 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

oj

unread,

Mar 10, 2002, 5:49:37 PM3/10/02

to

> Yes. I've seen people in the newsgroup posting a set of tables where
> each table have had a one-column primary key, which in most cases have
> been IDENTITY columns. Now, that is what I call bad table design.

The use of an identity column is certainly valid. Here is an excerpt from
BOL:

"For each table, a single identifier column can be created that contains
system-generated sequential values that uniquely identify each row within
the table. For example, an identifier column can generate unique customer
receipt numbers for an application automatically as rows are inserted into
the table. Identifier columns usually contain values unique within the table
on which they are defined."

-oj

Erland Sommarskog

unread,

Mar 10, 2002, 6:49:00 PM3/10/02

to

Don't get me wrong: I'm not saying that using IDENTITY at all is bad.
It's not. But using IDENTITY for all tables is not good design in my
book.

oj

unread,

Mar 10, 2002, 7:00:29 PM3/10/02

to

>
> Don't get me wrong: I'm not saying that using IDENTITY at all is bad.
> It's not. But using IDENTITY for all tables is not good design in my
> book.
>

Okay...I must have misread your initial post. Certainly, identity has its
place in db design. Having an identity column in EVERY single table and
making it a pk in every table is NOT a good design in my book either.

-oj

lindawie

unread,

Mar 10, 2002, 7:08:29 PM3/10/02

to

> Is the use of multiple columns as primary key a good
design?

It depends. Some people like to make this a religous issue
that can only be answered in absolute terms. That's hogwash.
The truth is, it depends on the business needs, the nature
of your application, developer skill level, project budget,
to name just a few factors.

> There are tables with combination of 2 or more columns
create a unique row.
> But I found this increase complexity in maintainence.

I agree that having multi-column primary keys can add
complexity to an application. And this can sometimes be a
problem. Additional complexity often equates to higher
development and maintenance costs.

In the end you will have to decide what is right for your
application.

Here are a few link to articles that objectively discuss the
pros and cons without the tiresome zealotry.

General articles:
http://www.dbpd.com/vault/9805xtra.htm
http://www.sqlmag.com/Articles/Index.cfm?ArticleID=5113&pg=2
http://www.bcarter.com/intsurr1.htm
http://www.inquiry.com/techtips/thesqlpro/10min/10min0899.as
p
http://www.aisintl.com/case/technical_keys.html

Oriented toward data warehousing, but much is still valid
for OLTP systems:
http://www.dbmsmag.com/9805d05.html
http://www.dbmsmag.com/9806d05.html
http://msdn.microsoft.com/library/default.asp?url=/library/e
n-us/createdw/createdw_10kz.asp

Robert Ellis

unread,

Mar 10, 2002, 10:59:27 AM3/10/02

to

In theoretical terms, there's nothing wrong with that.

If you create a unique, single column key as an alternative, you are
probably denormalizing your db design. That's theoretically bad, but in
practice quite common.

--
--
Kind Regards,

Robert A. Ellis, MCSD
Software Developer

"ABC" <nos...@nospam.com> wrote in message
news:edE$#AByBHA.1228@tkmsftngp04...