[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Purpose for unique indexes

Posted on 2011-09-02
8
Medium Priority
?
347 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 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 49

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 49

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

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

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

If you’re using QODBC to update QuickBooks data from Microsoft® Access but Access is not showing the updated data, you could have set up QODBC incorrectly.
Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

834 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