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!