Keys in SQL | Primary Key | Foreign Key| Candidate Key|Composite Key |Alternate Key|Super Key|Surrogate Key| Unique Key

In SQL, keys are used to identify rows in a table uniquely, enforce relationships between tables, and maintain data integrity. There are different types of keys, each serving a specific purpose. Below is a breakdown of various keys with simple examples:

1. Primary Key

A primary key uniquely identifies each record in a table. It cannot have NULL values, and there can only be one primary key per table.

Example:

CREATE TABLE Students (
StudentID INT PRIMARY KEY, -- Primary Key
Name VARCHAR(50),
Age INT
);

Here, StudentID is the primary key, meaning each StudentID value must be unique and non-null.

1. Foreign Key

A foreign key is a column (or set of columns) that creates a relationship between two tables. It points to the primary key of another table, enforcing referential integrity.

Example:

CREATE TABLE Courses (
CourseID INT PRIMARY KEY, -- Primary Key
CourseName VARCHAR(50)
);

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

Here, CourseID in the Enrollments table is a foreign key that references the CourseID in the Courses table.

1. Candidate Key

A candidate key is a column (or set of columns) that can uniquely identify a row in a table. A table can have multiple candidate keys, but only one of them is chosen as the primary key.

Example:

CREATE TABLE Employees (
EmployeeID INT, -- Candidate Key
Email VARCHAR(100), -- Candidate Key
Name VARCHAR(50),
PRIMARY KEY (EmployeeID) -- Chosen as the Primary Key
);

In this table, both EmployeeID and Email can uniquely identify a row, so they are candidate keys. However, EmployeeID is chosen as the primary key.

1. Composite Key

A composite key is a primary key composed of two or more columns, which together uniquely identify a row in the table.

Example:

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

Here, both OrderID and ProductID together form a composite key. No two rows can have the same combination of OrderID and ProductID.

1. Alternate Key

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

Example:

CREATE TABLE Vehicles (
VehicleID INT, -- Primary Key
LicensePlate VARCHAR(50), -- Alternate Key
OwnerName VARCHAR(50),
PRIMARY KEY (VehicleID)
);

Here, LicensePlate is a candidate key but not the primary key, so it is an alternate key.

1. Unique Key

A unique key ensures that all values in a column or set of columns are distinct from each other. Unlike the primary key, a unique key can have NULL values.

Example:

CREATE TABLE Users (
UserID INT PRIMARY KEY,
Email VARCHAR(100) UNIQUE, -- Unique Key
Username VARCHAR(50)
);

Here, Email is a unique key, ensuring that no two users have the same email address, but it can allow NULL values (if desired).

1. Super Key

A super key is a set of one or more columns that can uniquely identify a row in a table. A candidate key is a minimal super key (i.e., no column can be removed from it without losing the uniqueness property).

Example:

In the Employees table:

(EmployeeID, Email) -- Super Key (as EmployeeID and Email together can uniquely identify rows)

Any combination that can uniquely identify rows, such as EmployeeID or Email alone, or both together, can be considered a super key.

1. Surrogate Key

A surrogate key is an artificial key used as a unique identifier for a table. It's often a system-generated value like an auto-incrementing integer that has no real-world meaning.

Example:

CREATE TABLE Orders (
OrderID INT AUTO_INCREMENT, -- Surrogate Key
ProductName VARCHAR(50),
Quantity INT,
PRIMARY KEY (OrderID)
);

Here, OrderID is a surrogate key, typically generated by the database system, without any direct business meaning.

Summary of Keys:

Each of these keys plays a crucial role in designing well-structured databases that ensure data integrity and efficient querying.

The above is the detailed content of Keys in SQL | Primary Key | Foreign Key| Candidate Key|Composite Key |Alternate Key|Super Key|Surrogate Key| Unique Key. For more information, please follow other related articles on the PHP Chinese website!