HTML
JAVASCRIPT
CSS
PYTHON
JAVA
SQL

SQL Keys

SQL Keys – A Deep Dive

SQL keys are essential in relational databases, ensuring uniqueness, integrity, and proper relationships between tables. They help in identifying records, enforcing constraints, and optimizing database operations.

Types of SQL Keys

1. Primary Key

A Primary Key uniquely identifies each record in a table. It does not allow duplicate or NULL values.

Syntax:

CREATE TABLE Students (
    StudentID INT PRIMARY KEY,
    Name VARCHAR(100),
    Age INT,
    Course VARCHAR(50)
);

Insert a new student into the students table.

Example:

INSERT INTO Students (StudentID, Name, Age, Course) 
VALUES (101, 'Alice Green', 22, 'Computer Science');

2. Foreign Key

A Foreign Key establishes a relationship between two tables, linking a column in one table to the Primary Key in another table.

Syntax:

CREATE TABLE Enrollments (
    EnrollmentID INT PRIMARY KEY,
    StudentID INT,
    CourseID INT,
    FOREIGN KEY (StudentID) REFERENCES Students(StudentID)
);

Example:

INSERT INTO Enrollments (EnrollmentID, StudentID, CourseID) 
VALUES (1, 101, 502);

3. Unique Key

A Unique Key ensures that all values in a column are distinct, but unlike the Primary Key, it allows a single NULL value.

Syntax:

CREATE TABLE Faculty (
    FacultyID INT UNIQUE,
    Name VARCHAR(100),
    Department VARCHAR(50)
);

Example:

INSERT INTO Faculty (FacultyID, Name, Department) 
VALUES (201, 'Dr. Mark Spencer', 'Mathematics');

4. Candidate Key

A Candidate Key is a column or a set of columns that can serve as a Primary Key. A table may have multiple candidate keys, but only one can be chosen as the Primary Key.

Syntax:

CREATE TABLE Employees (
    EmpID INT,
    Email VARCHAR(100) UNIQUE,
    PhoneNumber VARCHAR(20) UNIQUE,
    PRIMARY KEY (EmpID)
);

Example:

INSERT INTO Employees (EmpID, Email, PhoneNumber) 
VALUES (301, 'john.doe@example.com', '9876543210');

5. Super Key

A Super Key is any set of columns that can uniquely identify a row. It can contain additional attributes beyond what is required for uniqueness.

Syntax:

CREATE TABLE Orders (
    OrderID INT,
    CustomerID INT,
    OrderDate DATE,
    PRIMARY KEY (OrderID)
);

Example:

INSERT INTO Orders (OrderID, CustomerID, OrderDate) 
VALUES (501, 1001, '2025-02-19');

6. Composite Key

A Composite Key consists of multiple columns that together create a unique identifier for a row.

Syntax:

CREATE TABLE OrderDetails (
    OrderID INT,
    ProductID INT,
    Quantity INT,
    PRIMARY KEY (OrderID, ProductID)
);

Example:

INSERT INTO OrderDetails (OrderID, ProductID, Quantity) 
VALUES (501, 702, 5);

7. Alternate Key

An Alternate Key is a Candidate Key that is not chosen as the Primary Key.

Example:

  In the Employees table above, Email and PhoneNumber are Candidate Keys, 
  but EmpID is the Primary Key. The remaining ones (Email and PhoneNumber) are Alternate Keys.

Conclusion

SQL keys play a fundamental role in structuring databases efficiently. Understanding their functions and using them appropriately ensures data integrity, consistency, and optimized performance.

Previous Next