What Is Keys? Describe its various types?

“Keys is a set of one or more columns whose combined values are unique among all occurrences in a given table. A key is the relational means of specifying uniqueness. In patient database a patient number could be used as a key field to uniquely identify each patient’s record in the doctor’s patient file. There must be at least one key field in each table. Sometimes a record may contain more than one key field for e.g. the doctor’s patient file may contain both a patient number an a national insurance number for each patient. Both of these are key fields.

TYPES OF KEYS:-

• Candidate key:- Candidate keys are those attributes of a relations, which have the properties of uniqueness and irreducibility. Let r be a relation. By definition, the set of all attributes of has the uniqueness property, meaning that, at any given time no two tuples in the value of r at that time are duplicates of one another. In student’s relation, the attribute roll no has this Property. Irreducibility property that if a candidate key is a composite key( consists of more than one attribute) then no individual attribute of candidate key, which participate into it is unique e.g. if the combination of (name, class) is unique, then it can be identified as the candidate key if and only if name & class initially are not unique.

• Super key: - A super key has the uniqueness property but not necessarily the irreducibility property e.g. if roll number is unique in relation student then. The set of attributes (roll number, name, and class) is a super key for a relation student.

• Primary key: - The primary key is an attribute or a set of attributes that uniquely identify a specific instance of an entity. Every entity in the data model must have a primary key whose value uniquely identify instance of the entity. Some time a record may contain more than one key field. For e.g. doctor patient file may contain both a patient number and a national insurance number for each patient. Both of these are key fields. We therefore choose one of them and call it the primary key field. Primary key cannot containing null value because we cannot uniquely identify multiple null values.

• Alternate key :- The alternate keys of any table are simply those candidate keys which are not currently selected as primary key.
• Composite key:- Sometimes it requires more than one attribute to uniquely identify an entity. A primary key that is made up of more than one attribute is known as a composite key.
• Foreign keys:- foreign keys are the attributes of a table, which refer to the primary key of some another table foreign keys permit only those values. Which appears in the primary key of the table to which it refers or may be null foreign keys are used to link together two or more different tables which have some form of relationship with each other. The foreign key is a reference to the tuple of a table from which it was taken, this tuple being called the references or target tuple. The table containing the referenced tuple will be called as target table.