Different keys in Databases

1.Primary Key:

A primary key uniquely identifies each record in a table. It must have unique values and cannot contain NULL.

CREATE TABLE Students (
    StudentID INT PRIMARY KEY,
    FirstName VARCHAR(50),
    LastName VARCHAR(50)
);        

2.Foreign Key:

• A foreign key is a field (or collection of fields) in one table that uniquely identifies a row of another table. It establishes a relationship between the two tables.

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

3.Composite Key:

A composite key is a primary key composed of multiple columns that together uniquely identify a record.

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

4.Super Key:

A super key is a set of one or more columns (attributes) that can uniquely identify a row in a table. A super key may have additional attributes that are not necessary for unique identification.

CREATE TABLE Products (
    ProductID INT,
    ProductCode VARCHAR(50),
    ProductName VARCHAR(100),
    PRIMARY KEY (ProductID),
    UNIQUE (ProductCode)
);        

5 Candidate key:

A candidate key is a column, or set of columns, that can qualify as a unique key in a database. There can be multiple candidate keys in a table..

CREATE TABLE Employees (
    EmployeeID INT,
    NationalIDNumber VARCHAR(50),
    Email VARCHAR(100),
    PRIMARY KEY (EmployeeID),
    UNIQUE (NationalIDNumber),
    UNIQUE (Email)
);        

6.Unique Key:

A unique key is a constraint that ensures all values in a column or a set of columns are unique. Unlike the primary key, unique keys can have NULL values.

CREATE TABLE Users (
    UserID INT PRIMARY KEY,
    Email VARCHAR(100) UNIQUE,  -- Unique key constraint
    Username VARCHAR(50) UNIQUE -- Another unique key constraint
);        

7.Alternate Key

An alternate key is any candidate key that is not chosen as the primary key.

CREATE TABLE Vehicles (
    VehicleID INT PRIMARY KEY,
    LicensePlate VARCHAR(20) UNIQUE,
    VIN VARCHAR(50) UNIQUE
);        

8.Natural key :

A natural key is a key that has a logical relationship to the attributes within that record, typically derived from the real-world domain.

CREATE TABLE Citizens (
    SSN CHAR(9) PRIMARY KEY,  -- Social Security Number as a natural key
    FirstName VARCHAR(50),
    LastName VARCHAR(50),
    DateOfBirth DATE
);        

9.Surrogate Key:

A surrogate key is an artificially generated key, often a sequential number, that has no intrinsic meaning but uniquely identifies a record.

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

10.Secondary Key:

A secondary key is a non-primary key used for data retrieval purposes and typically used in indexes to speed up queries.

CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    FirstName VARCHAR(50),
    LastName VARCHAR(50),
    DepartmentID INT
);
CREATE INDEX idx_department ON Employees(DepartmentID);