If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Key vs index?
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 |
#2
|
|||
|
|||
Key vs index?
Rover wrote in
: 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 |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
find keywords | Tennessee picker | General Discussion | 1 | July 7th, 2004 06:23 AM |
How to repair index corruption | chris | Database Design | 9 | June 25th, 2004 11:40 AM |
What VBA Stmt Will Tell Me the Index of a Chart's SERIES Formula? | SidBord | Charts and Charting | 2 | June 23rd, 2004 07:26 PM |
Indexing - Global refresh/deletion of index marks | Ray Cleere | Formatting Long Documents | 2 | April 30th, 2004 04:31 PM |
Indexing - Global refresh/deletion of index marks | Ray Cleere | Formatting Long Documents | 0 | April 30th, 2004 10:46 AM |