Primary Keys
The first type of key we’ll discuss is the primary key. The primary key uniquely identifies each record in a table. Every database table should have one or more columns designated as the primary key.
Primary Key Example
Assume we have a table called Employees that contains personnel information for every employee in our firm. We’d need to select an appropriate primary key that would uniquely identify each employee. Your first thought might be to use the employee’s name. However, this wouldn’t work because it’s conceivable that you’d hire two employees with the same name. A better choice might be to use the unique employee ID number assigned to each employee when hired. Some organizations use Social Security Numbers (or similar government identifiers) as the primary key because each employee already has one, and they’re guaranteed to be unique. However, the use of Social Security Numbers for this purpose is controversial due to privacy concerns. Once you decide on a primary key and set up the database, the database management system enforces the uniqueness of the key. For example, if you insert a record into a table with a primary key that duplicates an existing record, the insert will fail. Most databases are also capable of generating 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. While effective, this is a bad design practice because it leaves you with a meaningless value in each record in the table. Instead, use that space to store something useful.
Foreign Keys
Another type of key is the foreign key. The foreign key creates natural relationships between tables. Natural relationships exist between tables in most database structures.
Foreign Key Example
Returning to our Employees database, imagine that we want to add a table containing departmental information to the database. This new table might be called Departments and would contain a large amount of information about the department as a whole. We’d also want to include information about the employees in the department, but it would be redundant to have the same information in two tables (Employees and Departments). So instead, we would create a relationship between the two tables.
Creating a Relationship Between Tables
Let’s assume that the Departments table uses the Department Name column as the primary key. To create a relationship between the two tables, we would add a new column to the Employees table called Department. Next, we’d fill in the name of the department to which each employee belongs. Then, we would inform the database management system that the Department column in the Employees table is a foreign key that references the Departments table.
