How To Use Constraints in SQL

Introduction

When designing an SQL database, there may be cases where you want to impose restrictions on what data can be added to certain columns in a table. SQL makes this possible through the use of constraints. After applying a constraint to a column or table, any attempts to add data to the column or table that doesn’t align with the constraint will fail.

Different SQL implementations have their own unique ways of dealing with constraints. This guide provides an overview of the syntax that many database management systems use to manage constraints, using MySQL in examples throughout.

Prerequisites

In order to follow this guide, you will need a computer running some type of relational database management system (RDBMS) that uses SQL. The instructions and examples in this guide were validated using the following environment:

• A server running Ubuntu 20.04, with a non-root user with administrative privileges and a firewall configured with UFW, as described in our initial server setup guide for Ubuntu 20.04.
• MySQL installed and secured on the server, as outlined in How To Install MySQL on Ubuntu 20.04. This guide was verified with a newly-created user, as described in Step 3.

It will also be helpful to have a general understanding of what SQL constraints are and how they function. For an overview of this concept, you can follow our article on Understanding SQL Constraints.

You’ll also need a database you can use to practice creating tables with constraints. If you don’t have such a testing database, see the following Connecting to MySQL and Setting up a Sample Database section for details on how to create one.

Connecting to and Setting up a Sample Database

In case your SQL database system runs on a remote server, SSH into your server from your local machine:

ssh sammy@your_server_ip

Then open up the MySQL server prompt, replacing sammy with the name of your MySQL user account:

mysql -u sammy -p

Create a database named constraintsDB:

CREATE DATABASE constraintsDB;

If the database was created successfully, you’ll receive output like this:

Output
Query OK, 1 row affected (0.01 sec)

To select the constraintsDB database, run the following USE statement:

USE constraintsDB;

Output
Database changed

With that, you’re ready to follow the rest of the guide and begin learning about how to create and manage constraints in SQL.

Creating Tables with Constraints

Typically, you define constraints during a table’s creation. The following CREATE TABLE syntax creates a table named employeeInfo with three columns: empId, empName, and empPhoneNum. The statement also applies a UNIQUE constraint to the empId column. This will prevent any rows in the table must from having identical values in this column:

CREATE TABLE employeeInfo (
empId int UNIQUE,
empName varchar(30),
empPhoneNum int
);

This statement defines the UNIQUE constraint immediately after the empId column, meaning that the constraint applies only to that column. If you were to try adding any data to this table, the DBMS will check the existing contents of only the empId to ensure that any new values you add to empId are in fact unique. This is what’s referred to as a colum-level constraint.

You can also apply the constraint outside of the column definitions. The following example creates a table named racersInfo with three columns: racerId, racerName, and finish. Below the column definitions, it also applies a CHECK constraint to finish column to ensure that every racer has a finish greater than or equal to 1 (since no racer can place below first place):

CREATE TABLE racersInfo (
racerId int,
finish int,
racerName varchar(30),
CHECK (finish > 0)
);

Because the constraint is applied outside of any individual column definition, you need to specify the name of the columns you want the constraint to apply to in parentheses. Any time you specify a constraint outside of the definition of a single column, it’s known as a table-level constraint. Column-level constraints only apply to individual columns, but table constraints like this can apply to or reference multiple columns.

Naming Constraints

Whenever you define a constraint, your RDBMS generates a name for it automatically. This name is used to reference the constraint in error messages in commands used to manage constraints.

Sometimes, though, it’s convenient for database administrators to provide their own name for a constraint. Automatically-generated constraint names generally aren’t descriptive, so providing a name yourself can help you to remember a constraint’s purpose.

To name a constraint, precede the constraint type with the CONSTRAINT keyword followed by the name of your choice. This example statement recreates the racersInfo table, renaming it to newRacersInfo and adding noNegativeFinish as the name for the CHECK constraint:

CREATE TABLE newRacersInfo (
racerId int,
finish int,
racerName varchar(30),
CONSTRAINT noNegativeFinish 
CHECK (finish >= 1)
);

SHOW CREATE TABLE table_name;

\d table_name

Managing Constraints

In MySQL, you can add constraints to existing tables as well as delete them with ALTER TABLE statements.

For example, the following command adds a UNIQUE constraint to the empName column in the employeeInfo table created previously:

ALTER TABLE employeeInfo ADD UNIQUE (empName);

When adding a constraint to an existing table, you can also use the CONSTRAINT keyword to provide a name to identify the constraint. This example adds a UNIQUE constraint named uID to the racerId column from the racersInfo table created previously:

ALTER TABLE racersInfo ADD CONSTRAINT uID UNIQUE (racerId);

If, before adding a constraint like this, you inserted any records that would violate the condition of the new constraint, the ALTER TABLE statement will fail.

To delete a constraint, use the DROP CONSTRAINT syntax, followed by the name of the constraint you want to delete. This command deletes the racersPK constraint created in the previous command:

ALTER TABLE racersInfo DROP CONSTRAINT uID;

Conclusion

By reading this guide, you learned how to add and delete constraints to columns and tables using SQL. While the commands shown here should work on most relational databases, be aware that every SQL database uses its own unique implementation of the language. You should consult your DBMS’s official documentation for a more complete description of each command and their full sets of options.

If you’d like to learn more about working with SQL, we encourage you to check out the other tutorials in this series on How To Use SQL.