Key vs index?

[Rover]

I know this is table design 101, but I can't seem to find anything in
the help files...

What is the difference between a key and an index? Not only from a
usage standpoint, but how are the tables structured differently
internally? Can you point me to some publication?

TIA
Jim

 

[Tim Ferguson]

What is the difference between a key and an index?

Strictly, a key is a special kind of index; but in practice the words are
used very differently.

An index is some mechanism, implemented internally by the database engine,
that keeps track of the contents of a particular field (or combination of
fields) so that lookups on that field(s) are particularly efficient. This
comes at the cost of slower updates because all the indexes have to be
updated every time a record is touched.

A non-unique index, therefore, has no effect on data rules, but may or may
not be required depending on the DB engine, processor speed, application
requirements etc, etc.

A Unique Index has another function, which is to prevent two records having
the same value. This clearly has a major effect on what can be stored where
and is an important part of the db schema design. There are also the
access-speed advantages too like any other index but this is generally a
secondary issue.

A "key" is a Unique Index seen from the design point of view rather than
the application implementation. A table can have as many keys as it needs
(EployeeID, SSNumber, FName+LName (not a good idea!)) but it is fairly
uncommon to have more than one. You can't have no keys at all, because
otherwise you would not have a reliable method of finding one particular
record in a table.

A Primary Key is one key that is chosen to the usual key for identifying
records.

Not only from a
usage standpoint, but how are the tables structured differently
internally?

The internal structure of databases and files is really of interest only to
their systems programmers.

Can you point me to some publication?

Any stnadard text on R theory and DB Design. I am still a fan of CJH Date,
but then again I am ooooooooold..!

Was there a specific question you wanted to know about?

Hope that helps

Tim F

 

[Eric Butts [MSFT]]

Hi Jim,

An index helps Microsoft Access find and sort records faster.

The primary key of a table is automatically indexed.

The difference is the primary key must be unique as oppose to an index it
does not necessarily needs to be unique.

See:
http://support.microsoft.com/default.aspx?scid=KB;EN-US;304266

I hope this helps! If you have additional questions on this topic, please
respond back to this posting.


Regards,

Eric Butts
Microsoft Access Support
(e-mail address removed)
"Microsoft Security Announcement: Have you installed the patch for
Microsoft Security Bulletin MS03-026? If not Microsoft strongly advises
you to review the information at the following link regarding Microsoft
Security Bulletin MS03-026
<http://www.microsoft.com/security/security_bulletins/ms03-026.asp> and/or
to visit Windows Update at <http://windowsupdate.microsoft.com/> to install
the patch. Running the SCAN program from the Windows Update site will help
to insure you are current with all security patches, not just MS03-026."

This posting is provided "AS IS" with no warranties, and confers no rights

--------------------
| From: Rover <[email protected]>
| Newsgroups: microsoft.public.access.tablesdbdesign
| Subject: Key vs index?
| Date: Mon, 26 Jul 2004 08:59:23 -0400
| Organization: Posted via Supernews, http://www.supernews.com
| Message-ID: <[email protected]>
| User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.4)
Gecko/20030624 Netscape/7.1 (ax)
| X-Accept-Language: en-us, en
| MIME-Version: 1.0
| Content-Type: text/plain; charset=us-ascii; format=flowed
| Content-Transfer-Encoding: 7bit
| X-Complaints-To: (e-mail address removed)
| Lines: 10
| Path:
cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!news-out.cwix.com!newsfeed.cwix.co
m!news.maxwell.syr.edu!sn-xit-03!sn-xit-06!sn-post-01!supernews.com!corp.sup
ernews.com!not-for-mail
| Xref: cpmsftngxa06.phx.gbl microsoft.public.access.tablesdbdesign:82163
| X-Tomcat-NG: microsoft.public.access.tablesdbdesign
|
| I know this is table design 101, but I can't seem to find anything in
| the help files...
|
| What is the difference between a key and an index? Not only from a
| usage standpoint, but how are the tables structured differently
| internally? Can you point me to some publication?
|
| TIA
| Jim
|
|