Keys in DBMS

  • 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.