Difference between primary key and index

[Amir]

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]

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.

 

[John Vinson]

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]

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.