SQL Constraints: A Beginner’s Guide

One of the great features of databases is that they can restrict the types of values stored in a particular column. This means that you don’t need to validate every value before you try to insert it into a database.

That’s where SQL constraints come in. You can build in some initial validation using constraints which will check the integrity of a record before it is added to a database. In this tutorial, we’re going to discuss what constraints are and what constraints you can use in SQL.

Without further ado, let’s begin!

What is an SQL Constraint? 

A constraint limits the values that can be stored in a particular column in a table.

Constraints are useful because they help you preserve the accuracy of a database table. Using constraints allows you to make sure that each value you add into a column is formatted correctly before it is added. This can prevent complications due to improperly formatted data.

There are six main constraints in SQL:

• PRIMARY KEY
• FOREIGN KEY
• UNIQUE
• NOT NULL
• DEFAULT
• CHECK

These constraints are imposed when you create a table. This means that once a constraint is set, it will validate all the values you add into a table. You do not need to set a constraint for each record you add into a table.

SQL PRIMARY KEY Constraint

The PRIMARY KEY constraint uniquely identifies a row in a table. A table cannot hold more than one record with the same primary key. Primary key fields also cannot be null.

Let’s create a table called “employees” with an ID column. This ID column uses a primary key:

CREATE TABLE employees (
	id INT NOT NULL PRIMARY KEY,
	name VARCHAR(50),
	branch VARCHAR(50),
	email VARCHAR(50)
);

This table will have four columns: id, name, email, and branch. Every record must have a unique ID value. Otherwise, an error will be returned when you try to add in a record with a duplicate ID.

In this example, there are a few logical primary keys. Having a unique column called ID works because each employee has their own ID number. We could identify employees by their email address because no two employees will share an email address.

SQL FOREIGN KEY Constraint

A FOREIGN KEY is a combination of columns that creates a relationship between two tables.

Consider the following database. This database has two tables: employees and branches.

There is a value in the employees table called “branch_id”. This is a foreign key. This value links the employees and branches tables together. The value of branch_id in the employees table corresponds to the respective branch_id in the branches table.

You can create a FOREIGN KEY when you create a new table:

CREATE TABLE employees (
	id INT NOT NULL PRIMARY KEY,
	name VARCHAR(50),
	email VARCHAR(50),
	branch_id INT,
	FOREIGN KEY (banch_id) REFERENCES branches(branch_id)
);

This command creates a link between the employees and branches table. The column name of the foreign key that you want to create must exist in the other table. For instance, “branch_id” needs to exist in the “branches” table for our CREATE TABLE command to work.

SQL UNIQUE

The UNIQUE constraint ensures each value in a column has a unique value. This helps preserve the integrity of the data in a table.

The UNIQUE constraint is used when you want a column to contain unique values without imposing a primary key. Only one primary key can be assigned per table whereas you can use as many UNIQUE constraints as you want in a table.

Let’s create a table that uses the unique constraint:

CREATE TABLE employees (
	id INT NOT NULL PRIMARY KEY,
	name VARCHAR(50),
	branch VARCHAR(50),
	email VARCHAR(50) UNIQUE
);

The UNIQUE constraint is added after the type of data that a column should use. We’ve used UNIQUE to limit the value of “email” to contain only unique values. If we try to insert a duplicate email into the database, an error will occur.

SQL NOT NULL

The NOT NULL constraint states that a column cannot accept NULL values.

When a NOT NULL constraint is specified, you must add in a value for the column on which that constraint is imposed when you create a record. If you try to change the value of that column, you must specify a value that is not NULL.

Let’s create a table that uses the NOT NULL constraint:

CREATE TABLE employees (
	id INT NOT NULL PRIMARY KEY,
	name VARCHAR(50) NOT NULL,
	branch VARCHAR(50) NOT NULL,
	email VARCHAR(50) 
);

The NOT NULL keyword comes after the type of data a column uses. In this table, we’ve used three NOT NULL statements.

In order to add a record into our database, we must specify an id, the employee’s name, and the branch at which an employee works. These columns do not accept null values.

SQL DEFAULT

Have you ever wanted to set a default value for a column? The DEFAULT constraint has you covered. This table-level constraint sets a default value for a column in a database.

Default values are added into a record if you do not specify a value for a column in an INSERT statement. Consider this SQL CREATE TABLE statement:

"Career Karma entered my life when I needed it most and quickly helped me match with a bootcamp. Two months after graduating, I found my dream job that aligned with my values and goals in life!"

Venus, Software Engineer at Rockbot

Find Your Bootcamp Match

CREATE TABLE employees (
	id INT NOT NULL PRIMARY KEY,
	name VARCHAR(50),
	branch VARCHAR(50) DEFAULT "San Francisco",
	email VARCHAR(50) UNIQUE
);

By default, every record added into the employees table will have the branch name San Francisco. This will only be overridden if you explicitly specify a value for the branch column when you create a column.

The DEFAULT statement is commonly used with the NOT NULL constraint. This is because the DEFAULT statement allows you to create a record without a value. But, once that record has been created, you will not be able to set the value of the column to NULL.

SQL CHECK

The CHECK constraint checks whether a value inserted into a column meets a condition or set of conditions.

Our business only has two branches: San Francisco and Cambridge. We want to limit the values in the “branch” column so that only those two branch names can be used. We can do this using a CHECK statement:

CREATE TABLE employees (
	id INT NOT NULL PRIMARY KEY,
	name VARCHAR(50),
	branch VARCHAR(50) CHECK (branch = "San Francisco" OR branch = "Cambridge")
	email VARCHAR(50) UNIQUE
);

Every time a record is inserted into the employees table, the database will check to make sure the value of “branch” is either San Francisco or Cambridge. If another value is specified, an error will be returned.

CHECK statements are particularly useful because they reduce the prospects of a typo inside a column that can only have a certain range of values.

For instance, if the value of “branch” was set to be “San Ffrancisco”, an error would be returned. “San Ffrancisco” is not in our CHECK statement. Thus, the CHECK statement would have prevented the typo from being added to the database.

CHECK is not supported in MySQL.

Conclusion

SQL constraints limit the type of values that can be stored in a column. There are six main constraints in SQL: PRIMARY KEY, FOREIGN KEY, UNIQUE, NOT NULL, DEFAULT, and CHECK.

Constraints are imposed in a CREATE TABLE statement. This means that you don’t need to specify a constraint every time you create or update a record. The constraint is stored in the structure of the table itself.

Now you’re ready to start using SQL constraints like an expert!