Keys
A key is a single or combination of multiple fields. Its purpose is to access or retrieve data rows from the table account to the requirement.
- The keys are defined in the table to access / sequence the stored data quickly & smoothly.
- They are used to provides link b/w tables.
Purpose of Keys
- To ensure that each record in a table is precisely identified
- To help in establishing & enforcing various types of integrity constraints
- To establish table relationship
Types of Keys
- Super Key
- Candidate Key
- Primary Key
- Secondary/Alternate Key
- Composite/Concatenate/Compound Key
- Foreign Key
Super Key
It is a set of attributes of a relation variable for which it holds that in all relations assigned to that variable there are no two distinct tuples that have the same value for attributes in this set. A relation can have multiple superkeys.
- Super key is a key represents a column or set of columns that uniquely identifies a record.
- Any unique key with some non-unique key combination is called a superkey of that relationship.
Primary Key
- The attribute or combination of attribute that uniquely identifies a row or a record.
- It is the min. possible superkey ( irreducible super key )
- Non-redundant ( it does not have duplicate values )
Candidate Key
- The relation does not have two distinct tuples with the same values for these attributes.
- Candidate key is the min. super key which is comprised of more than one column.
There is no proper subset of these attributes for which one holds. Thus, a candidate key is a subset of Super key.
Composite/ Combination / Compound Key
Main role of Composite key is seen in case of joining two or more tables together.
- Composite primary keys are generated when no Primary key is designated in the database at time of import.
- Compound Primary keys occur when the DB schema specifically uses more than one column to form the primary key.
Eg.
Student( stu_no, stu_roll, stu_name, stu_dept ) // stu_roll is not Primary key only Unique Key
Exam( e_stu_id, e_stu_name, e_stu_per)
JOIN( stu_no, stu_roll, stu_name, stu_dept, e_stu_id, e_stu_per )
{ stu_roll,e_stu_id } Composite Key
Alternate / Secondary Key ( Null + Unique )
Candidate key which is not primary key is called secondary key.
- No, such key exists technically but if in a table already has a primary key & if we need another key to be unique so, declare it as a unique but can take null values.
Foreign Key
- It is an attribute or combination of attribute in a relation whose values match a primary key in another table (relation)
- The Table in which a foreign key is created is called Dependent table & the table to which it refers called Parent Table.
- It is a column or set of columns that refers to a Primary key in same table or another ( connection with table or within table )
- The method provides great flexibility in linking as it is independent of the physical links b/w records.
- A table may have multiple foreign keys. For each foreign key we can have diff. table ( referenced table )
