Types of Constraints in SQL with Syntax & Examples

Did you know there are roughly 19 million developers all around the world and out of them, about 7 million work with SQL? Since many professionals rely on this programming language for their database systems, it’s critical to make sure the data stored in these databases is accurate and trustworthy. This is where constraints in SQL come into play. They establish guidelines for how information can be used in tables, ensuring your data’s accuracy and integrity. In this blog post, we’ll delve deeper into understanding the syntax and purpose behind various types of constraints in SQL and how to implement them practically.

What are SQL Constraints?

Constraints in SQL are used to define rules for data stored in a table. They help maintain accuracy, integrity, and reliability by limiting what type of data can be entered into the table. If any violations occur between the constraint rules and the action performed on that specific piece of data, then it will not complete successfully. 

Constraints may be specified at either column or table level. If declared at the column level, they only apply to one particular field while those applied at a whole-table basis affect multiple columns in tandem with each other. If you’d like to learn more about SQL and SQL constraints example, we suggest that you take this comprehensive SQL course.

The Syntax of SQL Constraints

Given below is the syntax of SQL constraints.

CREATE TABLE table_name (
    column1 datatype constraint,
    column2 datatype constraint,
    column3 datatype constraint,
    ....
);

7 Different Types of Constraints in SQL

In this section, we will take a deeper look into the main types of SQL constraints and provide examples of how to create them.

1. NOT NULL Constraint in SQL

If you want to stop blank or null values from being entered into a certain column of an SQL table, the NOT NULL constraint can be used. This allows individuals to input a value when creating new rows in the database and prevents empty fields in this particular column. It is useful for situations where there must always be data within selected columns.

The below query creates a table Student with the fields ID and NAME set as NOT NULL, meaning that values must be provided for these two fields each time we attempt to insert a new row.

CREATE TABLE Student
(
ID int(6) NOT NULL,
NAME varchar(10) NOT NULL,
ADDRESS varchar(20)
);

CREATE TABLE Student
(
  ID int(6) NOT NULL,
  NAME varchar(10) NOT NULL,
  ADDRESS varchar(20)
);

INSERT INTO Student (ID, NAME, ADDRESS) VALUES (1, 'John', '123 Main St');

SELECT * FROM Student;

2. UNIQUE Constraint in SQL

The UNIQUE constraint prevents the same value from being entered twice into a specified column. For example: if you have a table called “Product” with columns, such as “Product_ID,” “Product_Name,” and Barcode”, then applying the UNIQUE constraint to the barcode field will ensure that each product has its distinct barcode number. Say someone attempts to insert an item using an already existing/used bar code. In this case, the system won’t accept this entry and display an error message instead. In other words, no duplicates are allowed for entries restricted by a unique constraint.

CREATE TABLE Product (
  Product_ID int NOT NULL,
  Product_Name varchar(50) NOT NULL,
  Barcode varchar(20) UNIQUE
);

INSERT INTO Product VALUES (1, 'Apple', '123456789');
INSERT INTO Product VALUES (2, 'Banana', '987654321');
INSERT INTO Product VALUES (3, 'Orange', '246813579');

-- Attempting to insert a duplicate barcode
INSERT INTO Product VALUES (4, 'Grapes', '123456789');

We create a “Product” table in the above code with columns for product ID, name, and barcode. We make sure that each value in the “Barcode” column is distinct by adding a UNIQUE constraint to it. Three products are then inserted into this table containing unique barcodes. If an attempt was made to insert another product that has already been registered (with its own ‘123456789’ barcode), it would be prevented from entering due to a violation of the previously enforced UNIQUE rule, resulting in an error message being displayed instead.

3. PRIMARY KEY Constraint

A primary key of a database table serves as a unique identifier for each row. It must have distinct values and cannot be null, combining the NOT NULL and UNIQUE constraints. This means no two rows in the same table can share identical values for their respective primary keys, plus all entries must contain an actual value instead of being blank or unknown.

CREATE TABLE Student (

  ID int PRIMARY KEY,

  Name varchar(50) NOT NULL,

  Age int,

  Email varchar(50) UNIQUE

);

We created a “Student” table that has four columns – ID, Name, Age, and Email. We used the PRIMARY KEY constraint to make sure each row in this table has its unique identifier in the form of an ‘ID’ number since no two rows can contain identical values for it. This is achieved by making this field both NOT NULL (no null values) and UNIQUE (each value must be different from all others).

4. FOREIGN KEY Constraint

A foreign key is a type of field in one table that links it to another by referencing the primary key of the other. This allows for each row in both tables to be identified uniquely through this link between them. For instance: 

Table: Employees

EmployeeID	EmployeeName	DepartmentID
1	John	101
2	Mary	102
3	David	101

Table: Departments 

DepartmentID	DepartmentName
101	Sales
102	Marketing
103	HR

In this example, the “Employees” table has a foreign key field, which is the “DepartmentID”. This relates to the primary key in another table called “Departments”. Now, this allows us to create an association between these two tables. To do this, we will use this SQL code when creating both of them with a statement specifying that the link should be established by assigning “DepartmentID” as its foreign key.

CREATE TABLE Departments (
  DepartmentID int PRIMARY KEY,
  DepartmentName varchar(50)
);

CREATE TABLE Employees (
  EmployeeID int PRIMARY KEY,
  EmployeeName varchar(50),
  DepartmentID int,
  FOREIGN KEY (DepartmentID) REFERENCES Departments(DepartmentID)
);

Two tables, “Departments” and “Employees”, are created by the code. The primary key of the Departments table is called DepartmentID. What links the two tables together is a foreign key that exists in Employees which references back to DepartmentID from Departments. This ensures any value for DepartmentID present in Employees points to a valid record from Departments.

5. CHECK Constraint

The CHECK constraint is one of the types of constraints in SQL that can be used to make sure that data meets certain requirements before it is inserted into a table. For example, if we have an Employees table and want only salaries between $20,000 and $100,000 to be stored in the salary column of this table, we could use the CHECK constraint with our CREATE TABLE statement for the Employee’s Table like this:

CREATE TABLE Employees (
    EmployeeID int PRIMARY KEY,
    Name varchar(50) NOT NULL,
    Salary decimal(10, 2) CHECK (Salary >= 20000 AND Salary <= 100000)
);

INSERT INTO Employees VALUES (1, 'John Doe', 40000.00);
INSERT INTO Employees VALUES (2, 'Jane Smith', 15000.00);
INSERT INTO Employees VALUES (3, 'Mark Johnson', 90000.00);
INSERT INTO Employees VALUES (4, 'Emily Brown', 110000.00);

SELECT * FROM Employees;

In this example, we created a table called ‘Employees’ with columns for EmployeeID, Name, and Salary. The column named Salary has rules set that the salary must be between $20,000 and $100,000. We then insert four records into this ‘Employees’ table. Two records are successful (with a value of $40k & 90k) but two fail because they need to meet the CHECK constraint’s conditions ($15k & 110K). Lastly, we view all existing data in our Employees Table to check which ones follow our rule.

6. DEFAULT Constraint

When you use the DEFAULT constraint with a SQL column, it will automatically insert the specified default value when no other value is provided. This means that if someone tries to add data without specifying any values for this particular column, then the DEFAULT value set up by you will be used instead. It helps ensure columns have some value in them and can help prevent unexpected empty fields from appearing in your database tables.

CREATE TABLE Employees (
    EmployeeID int PRIMARY KEY,
    Name varchar(50) NOT NULL,
    Department varchar(50),
    Salary decimal(10, 2) DEFAULT 50000.00
);

INSERT INTO Employees (EmployeeID, Name, Department, Salary) VALUES (1, 'John Doe', 'IT', 60000.00);
INSERT INTO Employees (EmployeeID, Name, Department) VALUES (2, 'Jane Smith', 'HR');
INSERT INTO Employees (EmployeeID, Name, Department, Salary) VALUES (3, 'Mark Johnson', 'Marketing', 50000.00);
INSERT INTO Employees (EmployeeID, Name) VALUES (4, 'Emily Brown');

SELECT * FROM Employees;

We create a table called “Employees” with columns for EmployeeID, Name, Department, and Salary. We assign the column ‘Salary’ to have a default value of $50,000.00 by using the DEFAULT constraint. Then we insert four records into this table but only specify different values depending on each record (e.g., the first one specifies all fields while the fourth just two). Finally, we get all data from Employees which includes the default salary value assigned before as well ($50K).

7. CREATE INDEX Constraint

In SQL, the CREATE INDEX constraint is used to make it easier for a database system to quickly retrieve data. This works like an index in a book where each page has its number or heading and you can find information faster by just looking at the numbers instead of reading through every single page one by one. When creating an index via this command, we tell our database which columns (or sometimes entire tables) should be indexed so that any queries involving those columns will now have improved performance. An example of CREATE INDEX constraints in SQL would look something like this.

CREATE TABLE Students (
  StudentID int PRIMARY KEY,
  Name varchar(50) NOT NULL,
  Course varchar(50),
  Grade decimal(5, 2)
);

INSERT INTO Students VALUES (1, 'John Smith', 'Mathematics', 85.5);
INSERT INTO Students VALUES (2, 'Emma Johnson', 'History', 92.0);
INSERT INTO Students VALUES (3, 'Michael Brown', 'Science', 78.8);
INSERT INTO Students VALUES (4, 'Sophia Davis', 'English', 88.2);

CREATE INDEX Students_Index
ON Students (Name, Course, Grade);

SELECT * FROM Students;

Conclusion

SQL is a language used to interact with databases. It helps structure and organizes data by setting certain rules, called constraints, which determine how the stored information should be handled. Constraints in SQL can include NOT NULL, UNIQUE, PRIMARY KEY, and FOREIGN KEY. Other helpful parameters are CHECK DEFAULT and CREATE INDEX, which help maintain accuracy when accessing or retrieving information in a database.