Purpose for unique indexes

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.
 
Frank FreeseAsked:
Who is Participating?
 
Patrick MatthewsConnect With a Mentor Commented:
>>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
 
Patrick MatthewsConnect With a Mentor Commented:
>>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
 
BusyMamaCommented:
Also, think of indexing almost as a filing system - it helps the application retrieve data more quickly.
0
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

 
Frank FreeseAuthor Commented:
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
 
Dale FyeConnect With a Mentor Commented:
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
 
Patrick MatthewsConnect With a Mentor Commented:
>>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
 
Frank FreeseAuthor Commented:
thnaks
0
 
Dale FyeCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.