Database Keys

0
610
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 )

                               

LEAVE A REPLY

Please enter your comment!
Please enter your name here