Differences between key superkey minimal superkey candidate key and primary key

Question

I m new to MySQL  and I m really confused about the different terms that I ve encountered  I tried googling the answer but the results are really confusing and when I try and understand it just seems like they are the same thing    What exactly are the differences among key  superkey  minimal superkey  candidate key and primary key

User · Answer

I have always found it difficult to remember all the keys; so I keep the below notes handy, hope they help someone! Let me know if it can be improved.

Key: An attribute or combination of attributes that uniquely identify an entity/record in a relational table.
PK: A single key that is unique and not-null. It is one of the candidate keys.
Foreign Key: FK is a key in one table (child) that uniquely identifies a row of another table (parent). A FK is not-unique in the child table. It is a candidate key in the parent table. Referential integrity is maintained as the value in FK is present as a value in PK in parent table else it is NULL.
Unique Key: A unique key that may or may not be NULL
Natural key: PK in OLTP. It may be a PK in OLAP.
Surrogate Key: It is the Surrogate PK in OLAP acting as the substitute of the PK in OLTP. Artificial key generated internally in OLAP.
Composite Key: PK made up of multiple attributes
SuperKey: A key that can be uniquely used to identify a database record, that may contain extra attributes that are not necessary to uniquely identify records.
Candidate Key: A candidate key can be uniquely used to identify a database record without any extraneous data. They are Not Null and unique. It is a minimal super-key.
Alternate Key: A candidate key that is not the primary key is called an alternate key.
Candidate Key/s with Extraneous data: Consider that can be used to identify a record in the Employee table but candidate key alone is sufficient for this task. So becomes the extraneous data.

Note that the PK, Foreign Key, Unique Key, Natural key, Surrogate Key, Composite Key are defined as Database objects; where the Natural key is a PK in the OLTP and could be a PK in the target OLAP. For the rest of the keys, it's up to the DB designer/architect to decide whether unique/not-null/referential integrity constraints need to enforced or not.

Below I have tried to use set theory to simplify the representation of the membership of the keys w.r.t. each other.

key = { All of the below keys   }
PK  = { PK  }
Foreign Key = { Key with Not Null constraint    }
Unique Key  = { {Candidate Key/s}, {attributes containing NULL} }
Natural key = { PK  }
Surrogate Key   = { PK  }
Composite Key   = { PK  }
Super Key   = { {Candidate Key/s}, {Candidate Key/s with Extraneous data}   }
Candidate Key   = { PK, {Alternate Key/s}   }
Alternate Key   = { {Candidate Keys} - PK   }
Candidate Key/s with Extraneous data    = {     }

I have summarized it below:

Notes: an-overview-of-the-database-keys-primary-key-composite-key-surrogate-key-et-al

User · Answer

Superkey - An attribute or set of attributes that uniquely defines a tuple within a relation  However  a superkey may contain additional attributes that are not necessary for unique identification   Candidate key - A superkey such that no proper subset is a superkey within the relation  So  basically has two properties  Each candidate key uniquely identifies tuple in the relation    amp  no proper subset of the composite key has the uniqueness property   Composite key - When a candidate key consists of more than one attribute   Primary key - The candidate key chosen to identify tuples uniquely within the relation   Alternate key - Candidate key that is not a primary key   Foreign key - An attribute or set of attributes within a relation that matches the candidate key of some relation

User · Answer

Here I copy paste some of the information that I have collected  Key A key is a single or combination of multiple fields  Its purpose is to access or retrieve data rows from table according to the requirement  The keys are defined in tables to access or sequence the stored data quickly and smoothly  They are also used to create links between different tables   Types of Keys  Primary Key The attribute or combination of attributes that uniquely identifies a row or record in a relation is known as primary key   Secondary key A field or combination of fields that is basis for retrieval is known as secondary key  Secondary key is a non-unique field  One secondary key value may refer to many records   Candidate Key or Alternate key A relation can have only one primary key  It may contain many fields or combination of fields that can be used as primary key  One field or combination of fields is used as primary key  The fields or combination of fields that are not used as primary key are known as candidate key or alternate key   Composite key or concatenate key  A primary key that consists of two or more attributes is known as composite key   Sort Or control key A field or combination of fields that is used to physically sequence the stored data called sort key  It is also known s control key          A superkey is a combination of attributes that can be uniquely used to identify a database record  A table might have many superkeys  Candidate keys are a special subset of superkeys that do not have any extraneous information in them   Example for super key  Imagine a table with the fields  lt Name gt    lt Age gt    lt SSN gt  and  lt Phone Extension gt   This table has many possible superkeys  Three of these are  lt SSN gt    lt Phone Extension  Name gt  and  lt SSN  Name gt   Of those listed  only  lt SSN gt  is a candidate key  as the others contain information not necessary to uniquely identify records   Foreign Key A foreign key is an attribute or combination of attribute in a relation whose value match a primary key in another relation  The table in which foreign key is created is called as dependent table  The table to which foreign key is refers is known as parent table

User · Answer

Primary key is a subset of super key  Which is uniquely define and other field are depend on it  In a table their can be just one primary key and rest sub set are candidate key or alternate keys

User · Answer

Candidate Key  The candidate key can be defined as minimal set of attribute which can uniquely   identify a tuple is known as candidate key  For Example  STUD NO in below   STUDENT relation          The value of Candidate Key is unique and non-null for every tuple    There can be more than one candidate key in a relation  For Example     STUD NO as well as STUD PHONE both are candidate keys for   relation    STUDENT    The candidate key can be simple  having only one attribute  or    composite as well  For Example   STUD NO  COURSE NO  is a composite   candidate key for relation STUDENT COURSE            Super Key  The set of attributes which can uniquely identify a tuple  is known as Super Key  For Example  STUD NO   STUD NO    STUD NAME  etc     Adding zero or more attributes to candidate key   generates super key  A  candidate key is a super key but vice versa is   not true  Primary Key   There can be more than one candidate key in a   relation out of which  one can be chosen as primary key  For Example    STUD NO as well as  STUD PHONE both are candidate keys for relation   STUDENT but STUD NO  can be chosen as primary key  only one out of   many candidate keys        Alternate Key  The candidate key other than primary key is called as  alternate key  For Example  STUD NO as well as STUD PHONE both are   candidate keys for relation STUDENT but STUD PHONE will be alternate    key  only one out of many candidate keys        Foreign Key  If an attribute can only take the values which are   present as values of some other attribute  it will be foreign key to   the attribute to which it refers  The relation which is being   referenced is called referenced relation and corresponding attribute   is called referenced attribute and the relation which refers to   referenced relation is called referencing relation and corresponding   attribute is called referencing attribute  Referenced attribute of   referencing attribute should be primary key  For Example  STUD NO in   STUDENT COURSE is a foreign key to STUD NO in STUDENT relation

User · Answer

Superkey  A superkey is a combination of attributes that can be uniquely used to identify a  database record  A table might have many superkeys Candidate keys are a special subset of superkeys that do not have any extraneous information in them   Examples  Imagine a table with the fields  lt Name gt    lt Age gt    lt SSN gt  and  lt Phone Extension gt   This table has many possible superkeys  Three of these are  lt SSN gt    lt Phone Extension  Name gt  and  lt SSN  Name gt  Of those listed  only  lt SSN gt  is a   candidate key    as the others contain information not necessary to uniquely identify records

User · Answer

SUPER KEY   Attribute or set of attributes used to uniquely identify tuples in the database   CANDIDATE KEY    Minimal super key is the candidate key Can be one or many Potential primary keys not null attribute or set of attributes to uniquely identify records in DB   PRIMARY KEY    one of the candidate key which is used to identify records in DB uniquely not null

User · Answer

Largely based on the accepted answer  but with a few tweaks to fit better the definitions taught in some courses     Key  A set of   1 columns  Superkey  A key that   a candidate key    Therefore  a superkey must contain   1 columns  Minimal Super key   Candidate Key  A key that can uniquely identify each row in a table  Primary Key  The chosen Candidate Key for doing that  Secondary key   Alternate key  A Candidate Key not chosen for doing that   Search Key  A key used for locating records  Composite key or concatenate key  A key with   1 columns    Usually implies  composite primary key   although  composite alternate key  is also a thing   Sort or control key  A key used to physically sequence the stored data  Foreign Key A key in one table that matches the Primary Key of another table     The table in which foreign key resides is called as dependent table  The table to which foreign key refers is known as parent table

User · Answer

Super Key   Super key is a set of one or more attributes whose values identify tuple in the relation uniquely  Candidate Key   Candidate key can be defined as a minimal subset of super key  In some cases   candidate key can not alone since there is alone one attribute is the minimal subset  Example  Employee id  ssn  name  addrress  Here Candidate key is  id  ssn  because we can easily identify the tuple using either id or ssn   Althrough  minimal subset of super key is either id or ssn  but both of them can be considered as candidate key  Primary Key   Primary key is a one of the candidate key   Example   Student Id  Name  Dept  Result  Here Super Key    Id  Id Name  Id Name Dept   because super key is set of attributes   Candidate Key   Id because Id alone is the minimal subset of super key  Primary Key   Id because Id is one of the candidate key

[key] Differences between key, superkey, minimal superkey, candidate key and primary key

Examples related to key

Examples related to terminology

Examples related to database-schema