Home‎ > ‎Database‎ > ‎

Primary key

The PRIMARY kEY is the main controlling field in your DATABASE  It is usually an integer that relates each field in the database.
When this value occurs in other tables as a reference to a particular row in the first table it is called a FOREIGN KEY.

PRIMARY KEY Constraints

A table typically has a column or combination of columns that contain values that uniquely identify each row in the table. This column, or columns, is called the primary key (PK) of the table and enforces the entity integrity of the table. You can create a primary key by defining a PRIMARY KEY constraint when you create or modify a table.

A table can have only one PRIMARY KEY constraint, and a column that participates in the PRIMARY KEY constraint cannot accept null values. Because PRIMARY KEY constraints guarantee unique data, they are frequently defined on an identity column.

When you specify a PRIMARY KEY constraint for a table, the Database Engine enforces data uniqueness by creating a unique index for the primary key columns. This index also permits fast access to data when the primary key is used in queries. Therefore, the primary keys that are chosen must follow the rules for creating unique indexes.

If a PRIMARY KEY constraint is defined on more than one column, values may be duplicated within one column, but each combination of values from all the columns in the PRIMARY KEY constraint definition must be unique.

Here is a good example of PRIMARY KEY constraints.

Composite PRIMARY KEY constraint
In this table both PRIMARY KEYS are unique and makes sure that there are not any repeat values.

Most databases are also capable of generating their own PRIMARY KEYS. Microsoft Access, for example, may be configured to use the AutoNumber data type to assign a unique ID to each record in the table.  We used this in some of our labs to get familiar with Access, while effective, this is a bad design practice because it leaves you with a meaningless value in each record in the table. Why not use that space to store something useful?  Like I said earlier a good primary key would be unique numbers or integers that can identify and relate each field in the database.

Choosing a PRIMARY KEY:
The selection of a primary key is one of the most critical decisions you’ll make in the design of a new database. The most important constraint is that you must ensure that the selected key is unique. If it’s possible that two records (past, present, or future) may share the same value for an attribute, it’s a poor choice for a primary key. When evaluating this constraint, you should think creatively.

  • ZIP Codes do not make good primary keys for a table of towns. If you’re making a a simple lookup table of cities, ZIP code seems to be a logical primary    key. However, upon further investigation, you may realize that more than one town may share a ZIP code. For example, four cities in New Jersey all share the ZIP code 07753.
  • Social Security Numbers do not make good primary keys for a table of people for many reasons. First, most people consider their SSN private and don’t want it used in databases in the first place. Second, some people don’t have SSNs – especially those who have never set foot in the United States! Third, SSNs may be reused after an individual’s death. Finally, an individual may have more than one SSN over a lifetime – the Social Security Administration will issue a new number in cases of fraud or identity theft.
Choosing the correct PRIMARY KEY is very essential, lucky for us Access if very good at identifying a unique part of your database.  Access is able to find a unique identifier that does not disrupt the flow or values of your database.  

 Attached is an example of what a PRIMARY KEY might look like, in this example the PRIMARY KEY are the catalog #s.  PRIMARY KEYS are often Account numbers, sequence numbers, or often (when relating to students) student ID #'s.

Jun 2, 2009, 3:22 PM