[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Purpose for unique indexes

Posted on 2011-09-02
8
Medium Priority
?
344 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 93

Assisted Solution

by:Patrick Matthews
Patrick Matthews earned 1400 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 48

Assisted Solution

by:Dale Fye
Dale Fye earned 600 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 93

Assisted Solution

by:Patrick Matthews
Patrick Matthews earned 1400 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 93

Accepted Solution

by:
Patrick Matthews earned 1400 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 48

Expert Comment

by:Dale Fye
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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

649 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