INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Composite Keys: Good or Bad?

Composite Keys: Good or Bad?

(OP)
I currently use Access 2007 and designing a database that appears to be simple.

My question is general in nature though. I feel I have a good natural composite key but I realize that I could use an auto-number type primary key field.

What are the pros and cons here? How do I know from the start which way to go? I have done my research with the client and understand the 'rules' for the situation well enough to create a composite key of three fields.

Any help would be greatly appreciated.

RE: Composite Keys: Good or Bad?

I always use an Autonumber primary key. I may set unique indexes on other fields to build in some integrity.

Duane
Hook'D on Access
MS Access MVP

RE: Composite Keys: Good or Bad?


"a composite key of three fields."

I hope you made sure those fields are 'set-in-stone' and never, ever change for any reason, right?

I would be still tempted to use an auto number anyway. I had too many cases where customer say: "Yes, it will never change" and come back and change the rules on me.

Have fun.

---- Andy

RE: Composite Keys: Good or Bad?

Composite primary keys complicate things later, when building the front end. Even a simple combo box is made more complicated. We have a rule against composite PKs where I work. Unique index the fields, but use an autonumber as the join field.

--Lilliabeth

RE: Composite Keys: Good or Bad?

If you have a composite key, you have a composite key. There's nothing you can do to change that. Whether you tell Access, or ask it to do anything about it, is a separate issue. You will still be able to use it as a key.

I'd be inclined to add an autonumber field as a surrogate because it's usually low cost. If however you use the surrogate for joins, then you've got the complication of propagating it to tables that reference it.

RE: Composite Keys: Good or Bad?

Let's look for a moment at what, exactly, a primary key is. "The primary key of a relational table uniquely identifies each record in the table." (http://databases.about.com/cs/administration/g/pri...) If, by the nature of your data, multiple fields are required to uniquely identify a record, then you have a composite key. If you have a composite key, your table should be configured accordingly. Adding an Identity (AutoNumber/AutoIncrement) field only masks the need for a composite key. In fact, using an Identity field, in place of a composite key, could hurt performance. In a table with a primary key defined, records are stored so that the PK fields are always sorted. Let's look at an example we can all, hopefully, relate to.

You have two tables: Orders and OrderLineItems. The Orders table will have a PK of OrderNumber, which is an AutoNumber. The OrderLineItems has a composite key: OrderNumber + ItemID + ShipLocation (because you ship to multiple locations on one order). Your employer has been very successful, and there are over 10 million records in the OrderLineItem table. Joining or filtering on the composite key will be very efficient because the records are sorted by these fields. Lets say you have used an AutoNumber field as the primary key. Finding a record by the OrderNumber + ItemID + ShipLocation attributes could take a long time since may not be in order.

Composite keys are not inherently bad. Sure, they can make programming the UI a little more complicated. However, it can cause real headaches if you don't configure your tables correctly in the first place.


GTG

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close