SQL Keys
Duration : 00:03:00
Super Key
A super key is a set of one or more attributes (columns) that can uniquely identify a tuple (row) in a relation (table). It may contain extra attributes that are not strictly required for uniqueness.Example
-- Creating a super key
CREATE TABLE Employees (
EmployeeID INT,
FirstName VARCHAR(50),
LastName VARCHAR(50),
Email VARCHAR(100),
PhoneNumber VARCHAR(15),
PRIMARY KEY (EmployeeID, Email)
);
Candidate Key
A candidate key is a minimal super key, meaning it is a set of attributes that uniquely identifies each tuple without any unnecessary attributes.Example
-- Creating a candidate key
CREATE TABLE Students (
StudentID INT,
SSN VARCHAR(9) UNIQUE,
FirstName VARCHAR(50),
LastName VARCHAR(50),
PRIMARY KEY (StudentID)
);
Primary Key
A primary key is a specific candidate key chosen as the main method of uniquely identifying tuples within a table. It enforces uniqueness and ensures that no NULL values are allowed in the key columns.Example
-- Creating a primary key
CREATE TABLE Orders (
OrderID INT PRIMARY KEY,
CustomerID INT,
OrderDate DATE
);
Alternate Key
An alternate key is a candidate key that was not chosen as the primary key. While it can still uniquely identify tuples, it is not used as the primary means of identification.Example
-- Creating an alternate key
CREATE TABLE Products (
ProductID INT PRIMARY KEY,
UPC VARCHAR(12) UNIQUE,
ProductName VARCHAR(100)
);
Composite/Compound Key
A composite or compound key is a key composed of two or more attributes. Together, these attributes uniquely identify tuples, but individually, they may not.Example
-- Creating a composite key
CREATE TABLE Orders (
OrderID INT,
ProductID INT,
PRIMARY KEY (OrderID, ProductID)
);
Unique Key
A unique key enforces uniqueness on a set of attributes but does not necessarily serve as the primary means of identification like a primary key.Example
-- Creating a unique key
CREATE TABLE Employees (
EmployeeID INT PRIMARY KEY,
SocialSecurityNumber VARCHAR(9) UNIQUE,
FirstName VARCHAR(50),
LastName VARCHAR(50)
);
Foreign Key
A foreign key is a field or set of fields in one table that references the primary key of another table, establishing a relationship between the two tables.Example
-- Creating a foreign key
CREATE TABLE Orders (
OrderID INT PRIMARY KEY,
CustomerID INT,
FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
);
Surrogate Key
A surrogate key is an artificially generated key, typically an integer, used as the primary key in a table. It has no inherent business meaning and is often used when natural keys are not suitable or practical.Example
-- Creating a surrogate key
CREATE TABLE Customers (
CustomerID INT PRIMARY KEY,
FirstName VARCHAR(50),
LastName VARCHAR(50)
);
Self-Referencing Foreign Key
A self-referencing foreign key is a foreign key within a table that references its own primary key, creating a hierarchical or recursive relationship within the same table.Example
-- Creating a self-referencing foreign key
CREATE TABLE Employees (
EmployeeID INT PRIMARY KEY,
ManagerID INT,
FirstName VARCHAR(50),
LastName VARCHAR(50),
FOREIGN KEY (ManagerID) REFERENCES Employees(EmployeeID)
);
Unique Constraint
A unique constraint ensures that the values in a specific column or combination of columns are unique across all rows in a table, similar to a unique key.Example
-- Creating a unique constraint
CREATE TABLE Products (
ProductID INT PRIMARY KEY,
ProductCode VARCHAR(20) UNIQUE,
ProductName VARCHAR(100),
Price DECIMAL(10, 2)
);
Still have some questions? Let's discuss.
CONTACT US