SQL CHEAT SHEET Series
SQL Constraints: Ensuring Data Integrity
In SQL, constraints are rules that are used to ensure the integrity of data in a database. Constraints can be applied to columns or tables, and they specify conditions that must be met before data can be added, updated, or deleted.When working with a database, it’s important to ensure the accuracy and consistency of the data stored within it.
One way to achieve this is through the use of SQL constraints. Constraints are rules that you can apply to a database table to ensure that the data entered into it meets certain requirements. They help maintain data integrity and prevent errors that can occur when data is inconsistent or invalid.To get started with SQL Constraints, down below you’ll see some of the types and some commands that you can use.
There are a lot of types of SQL constraints and some of them are: NOT NULL constraint, UNIQUE constraint, PRIMARY KEY constraint, FOREIGN KEY constraint and CHECK constraint. Here are a few essential SQL Constraints commands that you need to know:
NOT NULL CONSTRAINT
This constraint is used to ensure that a column cannot contain NULL values. When creating a table, you can specify that a column is NOT NULL by including the NOT NULL keyword after the column name.
Example:
CREATE TABLE Customers (
CustomerID INT PRIMARY KEY,
Name VARCHAR(255) NOT NULL,
Email VARCHAR(255) NOT NULL
);
In this example, both the Name and Email columns must have values entered into them, as they are marked as NOT NULL.
UNIQUE CONSTRAINT
This constraint is used to ensure that the values entered into a column are unique. When creating a table, you can specify that a column is UNIQUE by including the UNIQUE keyword after the column name.
Example:
CREATE TABLE Products (
ProductID INT PRIMARY KEY,
ProductName VARCHAR(255) NOT NULL,
ProductCode VARCHAR(255) UNIQUE
);
In this example, the ProductCode column must contain unique values. If an attempt is made to enter a duplicate value, an error will occur.
PRIMARY KEY CONSTRAINT
This constraint uniquely identifies each record in a table. When creating a table, you can specify that a column is the primary key by including the PRIMARY KEY keyword after the column name.
Example:
CREATE TABLE Orders (
OrderID INT PRIMARY KEY,
CustomerID INT,
OrderDate DATE
);
In this example, the ProductCode column must contain unique values. If an attempt is made to enter a duplicate value, an error will occur.
FOREIGN KEY CONSTRAINT
This constraint creates a relationship between two tables based on a common column. When creating a table, you can specify a foreign key by including the FOREIGN KEY keyword followed by the column name and the table and column it references.
Example:
CREATE TABLE Orders (
OrderID INT PRIMARY KEY,
CustomerID INT,
OrderDate DATE
FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
);
In this example, the Orders table references the Customers table using the CustomerID column. This ensures that each order is associated with a valid customer.
CHECK CONSTRAINT
This constraint ensures that a value entered into a column meets certain conditions. When creating a table, you can specify a check constraint by including the CHECK keyword followed by the condition that must be met.
Example:
CREATE TABLE Employees (
EmployeeID INT PRIMARY KEY,
FirstName VARCHAR(255),
LastName VARCHAR(255),
Salary DECIMAL(10,2) CHECK (Salary >= 0)
);
In this example, the CHECK constraint ensures that the value entered into the Salary column is greater than or equal to 0. If an attempt is made to insert a record with a negative salary, an error will occur. Note that the condition can be any valid SQL expression.
IN CONCLUSION:
SQL constraints play a crucial role in ensuring the integrity and accuracy of data in a database. By enforcing rules and restrictions on the data that can be inserted, updated, or deleted in a table, constraints help to prevent errors, inconsistencies, and data corruption. And we discussed several types of SQL constraints including NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, and CHECK constraints. Each type serves a different purpose and can be used in different situations to achieve data integrity. And that using constraints can greatly improve the quality of data in a database and can help prevent costly mistakes. It is important to carefully consider the appropriate constraints to apply to each table and column in order to maintain data integrity and accuracy.