Thursday 3 October 2019

What is Super Key, Candidate keys and Alternate Key?

What is Super Key, Candidate keys and Alternate Key?

Super Key
A Super key is any combination of fields within a table that uniquely identifies each record within that table.

Example:
managers (table)
ManagerID (unique)
Name
Title
DepartmentID

ManagerID+Name=Super Key
ManagerID+Title=Super Key


Candidate keys
Candidate keys are those keys which is candidate for primary key of a table. Candidate keys are such keys which full fill all the requirements of primary key which is not null and have unique records.

Example:
Suppose we have a table named Employee which has two columns EmpID and EmpMail, both are not null and have unique value.
Both Field are Candidate keys.



Alternate Key
If any table have more than one candidate key, then after choosing primary key from those candidate key, rest of candidate keys are known as an alternate key of that table.

Example:
Suppose we have a table named Employee which has two columns EmpID and EmpMail, both are not null and have unique value.
So both columns are treated as candidate key.
IF we make EmpID as a primary key, then EmpMail is known as alternate key.



Referential integrity
Referential integrity is a database concept that ensures that relationships between two table remain consistent.

Suppose one table has a foreign key to another table. Referential integrity makes ensure the both have valid record.

Example:
products (table)
    id (primary key)
    name 
    price
    sale_price
    size

reviews (another table)
    id (primary key),    
    product_id (foreign key)
    review by
    title
    start_rating

One Product can have one Or multiple reviews.
Referential integrity make sure following:
  1. You can't delete record from product table, till review exist in reviews table for same product Id.
  2. You can't add record in reviews table, till you have not added record in product table for same product Id.
  3. If you delete the record from product table, reviews will be deleted automatically using cascade for the same product Id.