SQL Candidate, Super, Primary, and Foreign Key Types with Examples

Understanding SQL Keys

In database management, keys play a crucial role in ensuring data integrity and establishing relationships between tables. SQL uses different types of keys, each with a specific purpose. Below, we discuss Primary Key, Candidate Key, Foreign Key, and Super Key with examples.

1. Primary Key

A Primary Key uniquely identifies each record in a table. It ensures that no two rows have the same value for this key, and it cannot contain NULL values. There can be only one primary key per table.

Example:

CREATE TABLE employee (
    employee_id INT PRIMARY KEY,
    name VARCHAR(100),
    address VARCHAR(255),
    phone VARCHAR(20)
);

Here, employee_id is the Primary Key as it uniquely identifies each employee.

2. Candidate Key

A Candidate Key is a minimal set of attributes that can uniquely identify a record. A table can have multiple candidate keys, but one is selected as the Primary Key.

Example:

CREATE TABLE emp (
    employee_id INT,
    name VARCHAR(100),
    address VARCHAR(255),
    phone VARCHAR(20),
    UNIQUE (employee_id)
);

Here, employee_id is a Candidate Key as it uniquely identifies records, but it is not yet the primary key.

3. Foreign Key

A Foreign Key is a field (or combination of fields) in one table that refers to the Primary Key in another table. It is used to establish relationships between tables and maintain referential integrity.

Example:

CREATE TABLE department (
    dept_id INT PRIMARY KEY,
    dept_name VARCHAR(100)
);

CREATE TABLE employee (
    employee_id INT PRIMARY KEY,
    name VARCHAR(100),
    dept_id INT,
    FOREIGN KEY (dept_id) REFERENCES department(dept_id)
);

Here, dept_id in the employee table is a Foreign Key referencing dept_id in the department table.

4. Super Key

A Super Key is a set of one or more attributes that uniquely identify a row in a table. A Candidate Key is a minimal Super Key, meaning it contains no unnecessary attributes.

Example:

CREATE TABLE student (
    student_id INT,
    name VARCHAR(100),
    email VARCHAR(100),
    phone VARCHAR(15),
    UNIQUE (student_id, email)
);

Here, (student_id, email) is a Super Key because it uniquely identifies a record.

Key Differences & Summary

Important Points:

• A Primary Key is a Candidate Key selected as the main unique identifier.
• Super Keys are broader than Candidate Keys.
• Foreign Keys establish relationships between tables.

By understanding these key types, you can effectively design and maintain relational databases in SQL.

FAQs on SQL Keys

1. What is the difference between a Primary Key and a Candidate Key?
   A Primary Key is a Candidate Key chosen as the table's unique identifier. A table can have multiple Candidate Keys, but only one Primary Key.

2. Can a Foreign Key be NULL?
   Yes, a Foreign Key can contain NULL values if it is not mandatory for every record to have a corresponding value in the referenced table.

3. Can a table have multiple Candidate Keys?
   Yes, a table can have multiple Candidate Keys, but only one can be set as the Primary Key.

4. What is the difference between a Primary Key and a Super Key?
   A Super Key is a set of attributes that uniquely identify a row, while a Primary Key is a minimal Super Key without unnecessary attributes.

5. Why do we need Foreign Keys in SQL?
   Foreign Keys ensure referential integrity by linking records across different tables, preventing orphan records.

By mastering these SQL keys, you can build well-structured, efficient, and secure databases. 🚀

Below is  some point of remaimber 

1. 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.
2. A Candidate key is minimal super key, i.e., no proper subset of Candidate key attributes can be a super key.
3. 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.
4. Foreign key is a field (or collection of fields) in one table that uniquely identifies a row of another table.a

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."

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

"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.