By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,505 Members | 1,511 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,505 IT Pros & Developers. It's quick & easy.

Indexes and Primary key

P: n/a
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
Share this Question
Share on Google+
1 Reply


P: n/a
"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.