Primary and foreign keys enable each row in the database tables to be identified, and enable relationships between the tables to be defined. These keys define the relational structure of a database.
Each table in a relational database may or may not have a primary key. The primary key is a column, or set of columns, that allows each row in the table to be uniquely identified. No two rows may have the same value of a primary key.
You can select a primary key from all of the available columns. Keep your primary key for each table as compact as possible. If possible, the primary key should be an unsigned integer data type, rather than char or varchar.
For example, the SalesOrderItems table in the demo database includes these columns:
An ID column that identifies the customer’s order.
A LineID column that provides an identification number for each item of the sales order.
A ProductID column that identifies the product that the customer ordered.
A Quantity column that shows how many items were ordered.
A ShipDate column that identifies the date the order shipped.
To identify a particular item, both the ID and the line LineID are required. The primary key is made up of both these columns.
The information in one table is related to that in other tables by foreign keys.
For example, the demo database has one table holding employee information and one table holding department information. The Departments table has these columns:
DepartmentID – An ID number that identifies the department. This is the primary key for the table.
DepartmentName – A column holding the name of the department.
DepartmentHeadID – The employee ID for the department manager.
To find the name of a particular employee's department, there is no need to put the name of the employee's department into the Employees table. Instead, the Employees table contains a column holding the employee's department ID. This is called a foreign key to the Departments table. A foreign key references a particular row in the table containing the corresponding primary key or unique constraint. The primary key and unique constraint so referenced are known as the candidate key.
In this example, the Employee table (which contains the foreign key in the relationship) is called the foreign table or referencing table. The Department table (which contains the referenced primary key) is called the primary table or the referenced table.