Solved

Purpose for unique indexes

Posted on 2011-09-02
8
337 Views
Last Modified: 2012-05-12
Experts,
I am seeking affirmation or clarification on indexes that serve to maintain data integrity and setting a primary key as AutoNumber.

As I understand indexes I can create an index that references up to six other fields making each field unique. This index is not a primary key but serves to maintain data integrity. The purpose for the primary key serves as a link in other tables. In other tables this would be known as a foreign key.
 
0
Comment
Question by:Frank Freese
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 92

Assisted Solution

by:Patrick Matthews
Patrick Matthews earned 350 total points
ID: 36473507
>>I can create an index that references up to six other fields making each filed unique

Not quite.  If you create a unique index that spans multiple columns, that enforces *unique combinations* of column values.  It does NOT enforce unique values in each column.  For that, you would need to have a set of unique indexes, one for each column involved.

>>The purpose for the primary key serves as a link in other tables

Not necessarily.  A primary key serves as a uniquely identifying value (or set of values, for a primary key spanning >1 column) for each row in the table.  You can have a primary key for a table even if that table is never related to another table.

>>In other tables this would be known as a foreign key.

Again, not quite.  A foreign key is a column on TableA that refers back to a primary key on TableB, and thus defines the relationship between the two tables.
0
 
LVL 7

Expert Comment

by:BusyMama
ID: 36473540
Also, think of indexing almost as a filing system - it helps the application retrieve data more quickly.
0
 

Author Comment

by:Frank Freese
ID: 36473570
In creating a named index with multiple columns the combination of the columns is unique? I did not mean to imply each column would be unique. The index in and of itself may or may not serve as a primary key. Is this more correct?

In creating a primary key I meant to say that one of its purposes is to be able link to another table as a foreign key and can also serve to maintain data integrity. Is this more correrct?
0
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 
LVL 47

Assisted Solution

by:Dale Fye (Access MVP)
Dale Fye (Access MVP) earned 150 total points
ID: 36473586
You raise a touchy subject with regard to Primary Keys.

There are those who believe that you should use "natural keys" (one or more columns of your data) as the primary key, and then there are those that believe in "surrogate keys" (normally an autonumber field).

These discussions/debates can get quite heated.  Search on either of these phrases to get a better understanding of Primary keys.  Here is an article where JDettman discusses the subject.
0
 
LVL 92

Assisted Solution

by:Patrick Matthews
Patrick Matthews earned 350 total points
ID: 36473685
>>I did not mean to imply each column would be unique.

Perhaps not, but that is what you wrote :)

>>The index in and of itself may or may not serve as a primary key. Is this more correct?

Yes.  Any unique index can be used as a primary key.

>>In creating a primary key I meant to say that one of its purposes is to be able link to another table as a
>>foreign key

Be careful here.  A column is never a single-column primary key AND a foreign key.  (That said, a column that is part of a multi-column primary key can also be a foreign key.)  Any column on TableA is a foreign key if and only if it refers back to a primary key on some other table.

>>and can also serve to maintain data integrity. Is this more correrct?

Better, but still not quite there.  A primary key's purpose is to uniquely identify a row.  That's it.
0
 
LVL 92

Accepted Solution

by:
Patrick Matthews earned 350 total points
ID: 36473701
>>There are those who believe that you should use "natural keys" (one or more columns of your data) as the
>>primary key, and then there are those that believe in "surrogate keys" (normally an autonumber field).

I tend to fall into the surrogate key camp, mainly because the "natural" keys one may want to use are typically subject to change, and IMHO if you ever have to change a primary key value, that means you screwed up the design :)
0
 

Author Closing Comment

by:Frank Freese
ID: 36473756
thnaks
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 36473811
I also fall into the "surrogate key" camp.  

Primarily because writing SQL to update or delete records that involve multi-column natural keys can be quite dangerous.  I once had a guy working for me that was using natural keys that involved 4 columns, and he wrote an update query that only included 3 of those columns in the WHERE clause, overwriting the values in dozens of other records.  Didn't take long to cure him of the multi-column natural key.
0

Featured Post

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

785 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question