You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
Table Keys
Each database table is consists of rows and columns. Some columns are special columns because they identify a particular record in a table. The special columns are called keys. The 2 most common keys are primary keys and foreign keys.
In a database table, the column that uniquely identifies each record in the table is called a primary key. It is almost always good design to define a primary key for all tables. In some cases, it is not possible to identify a column to be used as the primary key. In such cases, most designers create a dummy column which simply contains a running number to make each and every record in the table unique - for example, the AutoNumber data type in a Microsoft Access database.
Primary keys are also sometimes created from a combination of 2 or more columns. Such primary keys are called composite keys. Each column may not be unique by itself within the database table but when combined with the other column(s) in the composite key, the combination is unique.
When a database table is normalized to create an efficient and compact design, multiple tables are created that are linked together using foreign keys. Foreign keys are not necessarily unique in the table that stores them but they point to unique values in the referenced table.
To illustrate the concepts of the primary key and the foreign key, consider the sample table design below:
product table
category table
Let's assume that the above tables contain the following data:
Notice that in both the product table and the category table, the values in the product_id column and the category_id are never repeated. Both the product_id and the category_id columns are primary keys within their respective tables.
Also, notice that although the category_id column in the product table contains duplicate values, they point to records in the category table defined by unique category_id columns. The category_id column is a primary key in the category table but is a foreign key in the product table.
See also: Composite keys and Indexes
In a database table, the column that uniquely identifies each record in the table is called a primary key. It is almost always good design to define a primary key for all tables. In some cases, it is not possible to identify a column to be used as the primary key. In such cases, most designers create a dummy column which simply contains a running number to make each and every record in the table unique - for example, the AutoNumber data type in a Microsoft Access database.
Primary keys are also sometimes created from a combination of 2 or more columns. Such primary keys are called composite keys. Each column may not be unique by itself within the database table but when combined with the other column(s) in the composite key, the combination is unique.
When a database table is normalized to create an efficient and compact design, multiple tables are created that are linked together using foreign keys. Foreign keys are not necessarily unique in the table that stores them but they point to unique values in the referenced table.
To illustrate the concepts of the primary key and the foreign key, consider the sample table design below:
product table
| column | |
|---|---|
| product_id | primary key |
| description | |
| category_id | foreign key |
category table
| column | |
|---|---|
| category_id | primary key |
| description |
Let's assume that the above tables contain the following data:
| product_id | description | category_id |
|---|---|---|
| 0001 | table | 0001 | 0002 | chair | 0001 | 0003 | fan | 0002 |
| category_id | description |
|---|---|
| 0001 | furniture |
| 0002 | electrical |
Notice that in both the product table and the category table, the values in the product_id column and the category_id are never repeated. Both the product_id and the category_id columns are primary keys within their respective tables.
Also, notice that although the category_id column in the product table contains duplicate values, they point to records in the category table defined by unique category_id columns. The category_id column is a primary key in the category table but is a foreign key in the product table.
See also: Composite keys and Indexes
Advertising
Advertising: