SQl Candidate, Super, Primary, Foreign Key Types with Example

Primary Key

"Each record in a table is given a special identification by the PRIMARY KEY constraint. Primary keys cannot have NULL values and must have UNIQUE values. There can only be one main key in a table."

Below is example of Primary Key :
employee table -> employee(emplyee_id, NAME, ADDRESS, PHONE) , emplyee_id is a primary key

Candidate Key

A candidate key is the bare minimum set of attibute that can successfully identify a tuple. As An Example

emp table -> emp (emplyee_id, NAME, ADDRESS, PHONE)

 employee_id is the candidate key for relation emp.

"A subset of the super key known as a "candidate key" is capable of uniquely identifying the other table properties."

Every table must have at least a single candidate key.

Foreign Key

A field (or group of fields) in one table that refers to the PRIMARY KEY in another table is known as a FOREIGN KEY. The foreign key table is known as referenced or parent table.

Super Key

:A tuple's set of properties that can be used to identify it exclusively is referred to as a super key. A super key is a collection of one or more keys that distinguishes the rows in a database. It allows for NULL values.

"The super key is created by adding 0–more properties to the candidate key.
Super keys are candidate keys, but the opposite is not true."

Below is some point of remaimber

A super key is a set of attributes of a relation schema upon which all attributes of the schema are functionally dependent. No two rows can have the same value of super key attributes.
A Candidate key is minimal super key, i.e., no proper subset of Candidate key attributes can be a super key.
A Primary Key is one of the candidate keys. One of the candidate keys is selected as most important and becomes the primary key. There cannot be more that one primary keys in a table.
Foreign key is a field (or collection of fields) in one table that uniquely identifies a row of another table.