For an instructor lead, in-depth look at learning SQL click below.
SQL Keys are integral part of any relational database. They are used to identify a row or collection of rows in a table. The constraints add rules to the data that is insertable in the tables. In this post, we will explore different types of key constraints in SQL.
PRIMARY KEY
The PRIMARY KEY constraint uniquely identifies each record in a database table. Primary keys must contain unique values and cannot be null.
|
1 2 3 4 5 6 7 8 |
CREATE TABLE Customers ( CustomerID int NOT NULL, LastName varchar(255) NOT NULL, FirstName varchar(255), PRIMARY KEY (CustomerID) ); |
FOREIGN KEY
The FOREIGN KEY constraint prevents actions that would destroy links between tables. A FOREIGN KEY in one table points to a PRIMARY KEY in another table.
|
1 2 3 4 5 6 7 8 9 |
CREATE TABLE Orders ( OrderID int NOT NULL, OrderNumber int NOT NULL, CustomerID int, PRIMARY KEY (OrderID), FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID) ); |
UNIQUE KEY
The UNIQUE constraint ensures that all values in a column are different. Both the PRIMARY KEY and UNIQUE KEY constraints provide a guarantee for uniqueness for a column or set of columns.
|
1 2 3 4 5 6 7 8 |
CREATE TABLE Users ( UserID int NOT NULL, UserName varchar(255) NOT NULL UNIQUE, UserEmail varchar(255), PRIMARY KEY (UserID) ); |
COMPOSITE KEY
A COMPOSITE KEY is a combination of two or more columns in a table that can be used to uniquely identify each row in the table. Uniqueness is only guaranteed when the columns are combined; when taken individually the columns do not guarantee uniqueness.
|
1 2 3 4 5 6 7 8 |
CREATE TABLE Students ( StudentID int, CourseID int, Grade int, PRIMARY KEY (StudentID, CourseID) ); |
Mastering SQL key constraints is a vital part of becoming proficient in SQL and understanding database relationships. Practice and familiarization will aid in this understanding, leading to more efficient and effective database management.