Composite keys

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
lastname primary key
firstname primary key

The lastname column and the firstname column together form a composite key. Let's assume that the above tables contain the following data:
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