Solved

Purpose for unique indexes

Posted on 2011-09-02
8
335 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
 
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
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

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

707 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now