ASA Getting Started
Databases and Applications
Relational database concepts
Relations between tables
Each table in a relational database should have a primary key. The primary key is a column, or set of columns, that uniquely identifies each row. No two rows in a table may have the same primary key value.
In the sample database, the employee table stores information about employees. It has a primary key column named emp_id, which holds a unique ID number assigned to each employee. A single column holding an ID number is a common way to assign primary keys, and has advantages over names and other identifiers that may not always be unique.
A more complex primary key can be seen in the sales_order_items table of the sample database. The table holds information about individual items on orders from the company, and has the following columns:
id An order number, identifying the order the item is part of.
line_id A line number, identifying each item on any order.
prod_id A product ID, identifying the product being ordered.
quantity A quantity, displaying how many items were ordered.
ship_date A ship date, displaying when the order was shipped.
A particular sales order item is identified by the order it is part of, and by a line number on the order. These two numbers are stored in the id and line_id columns. Items may share a single id value (corresponding to an order for more than one item) or they may share a line_id number (all first items on different orders have a line_id of 1). No two items share both values, and so the primary key is made up of these two columns.
For a tutorial example, see Lesson 4: Identify and create primary keys.