What does the primary key do?

[sjn78]

Im trying to work out whether or not to assign a primary key to a selected column in an access database. I still have an ID field which is auto-numbered to allow a unique identifier between records.

 

So, does it really matter if the ID field has been set to the primary key or not? All database manipulation, queires, etc will be done via code.

 

I don't know a great deal about its purpose. Is it there acting as an index to speed up queries/record navigation....used for setting up relationships inside access... I hope someone can clear this up.

 

Thanks

[Afraits]

A primary key is the main index on a table, and as such will speed up queries and navigation especially when tables are linked via such fields. It is good practice to always have a primary key on a table.

[Nerseus]

A primary key should be made up of the unique field(s) in a table - the one "key" that can be used to identify a single row. If you have an IDENTITY column or AutoNumber column, then that will always be unique in the table and will usually be the PrimaryKey - but doesn't have to be. As a "key", it should also be the column that holds the value that you want to use in other tables - in those other tables it's the Foreign Key.

 

You MIGHT have a column for SSN in a customer table - if you want to ensure that the SSN is unique. But if you don't want the put the SSN in child tables (like an Address table) then you wouldn't make that your PrimaryKey.

 

An index is something different - it's a set of pointers to rows to speed up retrieval only. They slow down performance on INSERTs and UPDATEs since the index needs to be kept in sync.

 

Often times (and it's the default behavior in many DB's) the PrimaryKey will be set up as an index or even a clustered index. The DB is being smart and assuming that by making something a PrimaryKey, you'll be joining on that column and that usually means you want an index.

 

-ner

[Joe Mamma]

THEORETICALLY. . .

 

a primary key is the LOGICAL group of fields that together specify a unique LOGICAL entity that a table represents.

 

Remember a row in a table is a relation, that is, the data is related.

 

Often times, people erroneously use a numeric ID to be the primary key. . . The MS northwind Categories table is an example.

 

If I were desinging Northwind, I would make CategoryName the primary key, because CategoryName uniquely specifies a Category entity. I would then add the CategoryID as an autonumber and uniquely index that (called a surrogate key.)

 

There are many times that it is a combination of fields that specifies a unique entity - for example, imagine USCity and USState:

 

underline represents the primary key

 

Logically, usstate could be something like

usstate(name, abbrev, population, area)

 

and uscity could be something like

uscity(usstateAbbrev, name, population, area)

 

in usstate, I would make 'abbrev' not null uniquelly indexed (a surrogate key) and the physical structure would be identical. . .

 

Physcally, in uscity, I would add an autonumber ID field and make it not null uniquely indexed (another surrogate key) :

 

city(usstateAbbrev, name, population, area, ID)

 

Then, when making a child table relation, I would populate child tables with the surrogate key of uscity instead of bringing both primary key values to the child table . . . for example, a person:

 

usperson(SSN, firstname, lastname, birthcityID)

 

where birthcityID is the uscity they were born in.

 

again, the key idea here is that primary key is the LOGICAL combination of fields that specifies a unique entity represented by a table relation.

 

Logically, a State and Name, uniquely specify a US city.

 

Normalization and "Surrogate Key"

By the way. . . you can get a doctorate in this stuff. :)