To continue reading for free, register below or login
To read more you must become a member of SearchDataManagement.com
A primary key is a column (or columns) in a table that uniquely identifies the rows in that table.
CUSTOMERS
| CustomerNo | FirstName | LastName |
| 1 | Sally | Thompson |
| 2 | Sally | Henderson |
| 3 | Harry | Henderson |
| 4 | Sandra | Wellington |
For example, in the table above, CustomerNo is the primary key.
The values placed in primary key columns must be unique for each row: no duplicates can be tolerated. In addition, nulls are not allowed in primary key columns.
So, having told you that it is possible to use one or more columns as a primary key, how do you decide which columns (and how many) to choose?
Well there are times when it is advisable or essential to use multiple columns. However, if you cannot see an immediate reason to use multiple columns, then use one. This isn't an absolute rule, it is simply advice. However, primary keys made up of single columns are generally easier to maintain and faster in operation. This means that if you query the database, you will usually get the answer back faster if the tables have single column primary keys.
Next question — which column should you pick? The easiest way to choose a column as a primary key (and a method that is reasonably commonly employed) is to get the database itself to automatically allocate a unique number to each row.
In a table of employees, clearly any column like FirstName is a poor choice since you cannot control employee's first names. Often there is only one choice for the primary key, as in the case above. However, if there is more than one, these can be described as 'candidate keys' — the name reflects that they are candidates for the responsible job of primary key.
Now, given the definition above, that a primary key can be made up of more than one column and must uniquely identify the rows, we could choose, for example, EmployeeNo AND FirstName as the primary key. That fulfils the requirement but it is clearly foolish because we are adding complexity for no gain. It is also a great example of a super key. Super, in this case, is not a synonym for 'great' but a contraction of supernumerary. Super keys are to be avoided.
Foreign keys are columns that point to primary key columns. So, for example, OrderNo is the primary key of the table ORDERS below and CustomerNo is a foreign key that points to the primary key in the CUSTOMERS table.
ORDERS
| OrderNo | EmployeeNo | CustomerNo | Supplier | Price | Item |
| 1 | 1 | 42 | Harrison | $235 | Desk |
| 2 | 4 | 1 | Ford | $234 | Chair |
| 3 | 1 | 68 | Harrison | $415 | Table |
| 4 | 2 | 112 | Ford | $350 | Lamp |
| 5 | 3 | 42 | Ford | $234 | Chair |
| 6 | 2 | 112 | Ford | $350 | Lamp |
| 7 | 2 | 42 | Harrison | $235 | Desk |
If we want to be really pedantic, foreign keys don't have to point to a primary key. The only true requirement of the column at which a foreign key points is that it must contains unique values. Imagine, for example, that our employee tables looked like this:
EMPLOYEES
| SSecurityNo | EmployeeNo | FirstName | LastName | DateOfBirth | DateEmployed |
| AF-23432334 | 1 | Manny | Tomanny | 12 Apr 1966 | 01 May 1999 |
| DQ-65444444 | 2 | Rosanne | Kolumns | 21 Mar 1977 | 01 Jan 2000 |
| GF-54354543 | 3 | Cas | Kade | 01 May 1977 | 01 Apr 2002 |
| JK-34333432 | 4 | Norma | Lyzation | 03 Apr 1966 | 01 Apr 2002 |
| VB-48565444 | 5 | Juan | Tomani | 12 Apr 1966 | 01 Apr 2002 |
| FG-23566553 | 6 | Del | Eats | 01 May 1967 | 01 May 2004 |
The social security number is actually the primary key of the table but we issue each employee with a number which is also unique. Under these circumstances, ORDERS.EmployeeNo can be a foreign key pointing to EMPLOYEES.EmployeeNo; even though that column is not a primary key. So the actual rule is slightly more subtle. A foreign key must point to a candidate key.
A candidate key is a column that meets all of the requirements of a primary key. In other words, it has the potential to be a primary key.
Having told you this, I can also tell you that I cannot remember the last time I saw this done in a real, live, production database. In practice, foreign keys are almost invariably pointed at primary keys.
Most of this answer was shamelessly plagiarized from Inside Relational Databases, a book by by Mark Whitehorn and Bill Marklyn, published by Springer Verlag. However, since Mark is one of the authors of the book, the chances of him suing himself are low.
|
