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