Problem solve Get help with specific problems with your technologies, process and projects.

What is a primary key? Composite PK? Foreign key? Tuple?

>What is/are primary keys? what is composite primary key? what are foreign keys? what are tuples?

A primary key is a table column that can be used to uniquely identify every row of the table. Any column that has this property will do -- these columns are called candidate keys. A table can have many candidate keys but only one primary key. The primary key cannot be null.

Consider the following table --

FooNumber  FirstName  LastName  BarTab
   21       Fred       Jones      47
   32       Bill       Smith      23
   87       Wendy      Jones       -          
   32       Bob        Stikino   943

In this example, only FirstName is a single-column candidate key, because it is the only column that is unique and not null.

A composite primary key is a primary key consisting of more than one column. In the above example, the combinations (RecordNo,FirstName), (RecordNo,Lastname), (RecordNo,FirstName,Lastname), and (FirstName,LastName) are all candidate keys. Any combination including Age is not a candidate key because it contains a null.

Often, database designers add an extra column to their table designs, a column defined as an integer, which will hold a number. In Microsoft Access, this is an autonumber, in MySQL it's an auto-increment, in Oracle it's a sequence, and in SQL/Server it's an identity column. As these names suggest, this integer is automatically assigned by the database, usually incrementally, sometimes using an initial value and increment that you can specify. Some databases allow these numbers to be generated randomly.

The purpose of this type of automatically generated number is to act as the surrogate primary key, usually in those situations similar to the above where candidate keys are multi-column. The awkwardness of a multi-column candidate key becomes apparent as soon as you define a foreign key on it.

A foreign key is a column, or combination of columns, that contain values that are found in the primary key of some table (including, possibly, itself). A foreign key may be null, and almost always is not unique.

That last statement may be counterintuitive, so let's take another example. Here we have two tables that are related via a foreign key --

ClrPK  ColourName
 10      yellow
 20      red
 30      green
 40      blue

AdjPK AdjectiveName ClrFK 904 angry 20 913 envious 30 937 lazy - 941 lonely 40 954 fearful 10 979 jealous 30 991 furious 20

As you can see, the ClrFK column in the second table is a foreign key to the ClrPK primary key in the first table. Notice that the ClrPK values are unique and not null, but the ClrFK values may be null and often repeat. A null foreign key means that that particular row does not participate in the relationship. The fact that many foreign key values repeat simply reflects the fact that it's a one-to-many relationship.

In a one-to-many relationship, the primary key has the "one" value, and the foreign key has the "many" values. The trick to remembering this is to keep in mind that the primary key must be unique.

Dig Deeper on Oracle database design and architecture