A key is that data item that exclusively identifies a record.
The value of a key attribute can be used to identify uniquely each tuple in the relation.
A key is determined from the meaning of the attributes, and the property is time-invariant: It must continue to hold when we insert new tuples in the relation.
Types of keys
Super Key
Primary Key
Candidate Key
Composite Key
Foreign Key
Secondary Key
Super Key
A super key is a set of attributes whose values can be used to uniquely identify a tuple within a relation.
A relation may have more than one super key, but it always has at least one: the set of all attributes that make up the relation.
A super Key for an entity is a set of one or more attributes whose combined value uniquely identifies the entity in the entity set.
Primary Key
The primary key of a relation is a candidate key especially selected to be the key for the relation.
In other words, it is a choice, and there can be only one candidate key designated to be the primary key.
The primary key of a relation can be said to be a minimal super key.
Candidate Key
A candidate key is a super key that is minimal; that is, there is no proper subset that is itself a superkey.
A relation may have more than one candidate key, and the different candidate keys may have a different number of attributes. In other words, you should not interpret 'minimal' to mean the super key with the fewest attributes.
A candidate key has two properties:
in each tuple of R, the values of K uniquely identify that tuple (uniqueness)
no proper subset of K has the uniqueness property (irreducibility).
Composite Key
In many cases, as we design a databae, we will have tables that will use more than one column as part of the primary key. These are called composite keys or concatenated keys.
In other words, when a record cannot be uniquely identified by a single field, in such cases a composite key is used.
Foreign Key
In a relational database, the foreign key of a relation may be the primary key of another relation.
The attribute(s) within one relation that matches a candidate key of another relation. A relation may have several foreign keys, associated with different target relations.
Foreign keys allow users to link information in one relation to information in another relation. Without FKs, a database would be a collection of unrelated tables.
Secondary Key
A Secondary Key is an attribute or combination of attributes that may not be a candidate key but classifies the entity set on a particular characteristic.