When a primary key is created from a combination of 2 or more columns, the primary key is called a composite key. 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.
To illustrate the concept of the composite key and the foreign key, consider the sample table design below:
customer table
| column | |
| lastname |
primary key |
| firstname |
primary key |
| dateofbirth |
|
The lastname column and the firstname column together form a composite key. Let's assume that the above tables contain the following data:
| lastname | firstname | dateofbirth |
| henry |
john |
03/05/1960 |
| henry |
adam |
06/08/1974 |
| kidman |
adam |
04/01/1955 |
| bailey |
harry |
05/05/1980 |
| morgan |
alex |
09/09/1975 |
Notice that in the lastname column, there are 2 records with the value 'henry' and in the firstname column, there are 2 records with the value 'adam'. However, there are no records in the database table with a duplicate combination of both the lastname and the firstname.
BTW, the above table design is very problematic, sooner or later you will have customers changing their names or customers with exactly the same name...
See also:
Primary keys,
Foreign keys and
Indexes
