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:
- 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.
- 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.
- 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.
- 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.
- 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.
- 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).
- 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.
- 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.
Top comments (0)