Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion GroupsFormsForms ProgrammingQueriesModules / DAO / VBAReports / PrintingMacrosDatabase DesignSecurityConversionImporting / LinkingSQL Server / ADPMultiuser / NetworkingReplicationSetup / ConfigurationDeveloper ToolkitsActiveX ControlsNew UsersGeneral 1General 2
Access DirectoryToolsTutorialsUser Groups
Related Topics
SQL ServerOther DB ProductsMS OfficeMore Topics ...

MS Access Forum / Database Design / January 2005

Tip: Looking for answers? Try searching our database.

Difference between primary key and index

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Amir - 29 Jan 2005 23:55 GMT
What is the difference between primary key and index in Access?
If I define an Index field which is Unique and Not allowed Null, is it a the
same as a PK?
Rick Brandt - 30 Jan 2005 00:38 GMT
> What is the difference between primary key and index in Access?
> If I define an Index field which is Unique and Not allowed Null, is it a the
> same as a PK?

It's pretty darn close.  A table can have multiple unique indexes, but only one
PK.  For all practical purposes the PK is a unique constraint that doesn't allow
nulls.  There are some database engines that will impose the not null constraint
and the unique constraint of the PK without also building an index against it,
but most engines automatically index the PK.

Signature

I don't check the Email account attached
to this message.     Send instead to...
RBrandt    at       Hunter      dot      com

John Vinson - 30 Jan 2005 01:44 GMT
>What is the difference between primary key and index in Access?

A Primary Key is a logical construct. An Index is an area on your hard
disk which Access manages, which can (among other things) be used to
implement the logical construct of a Primary Key.

>If I define an Index field which is Unique and Not allowed Null, is it a the
>same as a PK?

Not necessarily, though the differences are subtle; it will prevent
duplicate records and can be used to create enforced one-to-many or
one-to-one relationships, just like a PK.

                 John W. Vinson[MVP]    
Chris2 - 30 Jan 2005 03:06 GMT
> What is the difference between primary key and index in Access?
> If I define an Index field which is Unique and Not allowed Null, is it a the
> same as a PK?

And my two cents:

A Primary Key is the column or combination of columns (Access refers
to them as "fields") that *uniquely* defines a row in a table.  No
true table is without a Primary Key, although most relational database
software products allow it.

An Index is a file-feature that is used for various things, like
making lookups more rapid, for enforcing uniqueness in a column, etc.

Access uses indexes to enforce Primary Keys and Foreign Keys (for
"Referential Integrity").

And, a litte more:

A Foreign Key is a column or combination of columns in one table, that
are also found in another table, where Access uses an index to make
sure that no value(s) in the Foreign Key column(s) exists that does
not also exist in those same columns in the "other" table.  This
process is called "enforcing Referential Integrity", and is used, for
example, to make sure that a database doesn't have customer phone
numbers hanging around in the PhoneNumbers table with CustomerID
values that don't exist in the Customers table.

Sincerely,

Chris O.
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.