How To Use Constraints in SQL
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.
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.
Note: Please note that many RDBMSs use their own unique implementations of SQL. Although the commands outlined in this tutorial will work on most RDBMSs, the exact syntax or output may differ if you test them on a system other than MySQL.
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.
You can also use an interactive terminal that is embedded on this page to experiment with the sample queries in this tutorial. Click the following
Launch an Interactive Terminal! button to get started.
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. If you are using the embedded interactive terminal on this page, note that the password to use when prompted is the word
- mysql -u sammy -p
Create a database named
- CREATE DATABASE constraintsDB;
If the database was created successfully, you’ll receive output like this:
OutputQuery OK, 1 row affected (0.01 sec)
To select the
constraintsDB database, run the following
- USE constraintsDB;
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:
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:
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.
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
- CREATE TABLE newRacersInfo (
- racerId int,
- finish int,
- racerName varchar(30),
- CONSTRAINT noNegativeFinish
- CHECK (finish >= 1)
Note: If you don’t set a name for a constraint, or you do but forget it later on, you’ll likely be able to find the name by consulting your database management system’s information schemas. Many modern database systems and clients even provide a shortcut to display internal
CREATE statements that indicate a constraint’s name.
Here are links to the official documentation for the relevant shortcut for MySQL and PostgreSQL:
- MySQL: MySQL includes the
SHOW CREATE TABLEstatement, which returns the entire
CREATE TABLEstatement that created the named table:
- SHOW CREATE TABLE table_name;
- PostgreSQL: The PostgreSQL client
psqlhas a number of options you can use to reveal information about a given table. The
\doption returns metadata of the named table:
- \d table_name
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;
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.