For an instructor lead, in-depth look at learning SQL click below.
SQL constraints are rules that are used to limit the type of data that can be stored in a table. Constraints provide a way to enforce the integrity of a database, ensuring that the data in a table adheres to a certain rule or specification. These constraints can be applied at the column level or the table level.
Types of SQL Constraints
There are several types of SQL constraints that can be applied when creating a table. These constraints include:
- NOT NULL: This constraint ensures that a column cannot have a NULL value.
- UNIQUE: The UNIQUE constraint ensures that all values in a column are different.
- PRIMARY KEY: A PRIMARY KEY constraint uniquely identifies each record in a table.
- FOREIGN KEY: A FOREIGN KEY is a field (or collection of fields) in one table, which refers to the PRIMARY KEY in another table.
- CHECK: The CHECK constraint ensures that all values in a column satisfies certain conditions.
Examples of SQL Constraints
Let’s go through some examples on how to use these constraints in SQL queries.
|
1 2 3 4 5 6 7 8 |
CREATE TABLE Employees( ID INT PRIMARY KEY, FirstName VARCHAR(30) NOT NULL, LastName VARCHAR(30) NOT NULL, Email VARCHAR(50) UNIQUE ); |
In this example, we have an Employees table with four columns: ID, FirstName, LastName, and Email. The ID field is the primary key and therefore must be unique. The FirstName and LastName fields can’t be NULL, ensuring that these fields will always have values. The Email field is unique, ensuring that no two employees can have the same email.
|
1 2 3 4 5 6 7 8 |
CREATE TABLE Orders( OrderID INT PRIMARY KEY, OrderNumber INT NOT NULL, EmployeeID INT, FOREIGN KEY (EmployeeID) REFERENCES Employees(ID) ); |
This is another example where we have an Orders table with an employee id as a FOREIGN KEY. It refers to the Employees table, meaning that all EmployeeIDs in the Orders table must also be present in the Employees table.
|
1 2 3 4 5 6 |
CREATE TABLE Product( ProductID int NOT NULL, Quantity int CHECK (Quantity > 0) ); |
The above example demonstrates how to use the CHECK constraint where the quantity of the products should always be greater than 0.
Conclusion
SQL constraints are a powerful way to ensure that the data stored in your database adhere to the rules you set. This also helps to maintain the accuracy and integrity of the data. By using these SQL constraints effectively, you can prevent invalid data entry into your tables and create more robust databases.