469,356 Members | 2,322 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,356 developers. It's quick & easy.

Indexes and Primary key

Hello

I'm trying to find the best way to set indexes and primary keys on MSAccess
tables

What are the advantages and disadvantages of indexes and primary keys?
What fields should be indexed?
Should I index more than one field per table?
What fields should have the primary key?

and so on

Is there any good reading material on this available?
Thanks
G Gerard
Apr 18 '06 #1
1 1667
"GGerard" <gg*****@nbnet.nb.ca> wrote in
news:In*********************@ursa-nb00s0.nbnet.nb.ca:
Hello

I'm trying to find the best way to set indexes and primary
keys on MSAccess tables

What are the advantages and disadvantages of indexes and
primary keys? What fields should be indexed?
Should I index more than one field per table?
What fields should have the primary key?

and so on

Is there any good reading material on this available?
Thanks
G Gerard

The primary key to a table is a field or combination of fields
which uniquely identify a row in that table. You can often use
an autonumber field as a surrogate primary key. Some people will
say this is correct, others will claim that the natural primary
key is best. Personally, being an old dBase III programmer, I
avoid autonumbers. You may have database designs where you have
multiple candidates for primary key. These should be indexed.

As to general indexes, the idea is that if you use a field to
sort the table, or a combination of fields, these fields or
groups should be indexed, even if they are not the primary key.
You should also index fields and groups that are used to filter
the table.on a regular basis.

Some claim that the foreign keys in a table should be indexed
too. Foreign keys are the references used as the relation to
another table's primary key. Access apparently does this
automatically when you set a relation, but I don't have any
reason to believe that this is important. .

For reading material, any good textbook on relational database
design will discuss these topics in greater detail, try your
public or university library.

This web article is also good:
http://www.databasejournal.com/sqlet...le.php/1469521

--
Bob Quintal

PA is y I've altered my email address.
Apr 18 '06 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

7 posts views Thread by robertbrown1971 | last post: by
2 posts views Thread by alex.mcshane | last post: by
5 posts views Thread by Shiraz | last post: by
2 posts views Thread by Lyle Fairfield | last post: by
22 posts views Thread by Zamdrist | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.