How To Use Constraints in SQL

Published on September 15, 2020
Default avatar

By Mark Drake

Manager, Developer Education

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:

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.

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:

  1. ssh sammy@your_server_ip

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

  1. mysql -u sammy -p

Create a database named constraintsDB:

  1. CREATE DATABASE constraintsDB;

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

Query OK, 1 row affected (0.01 sec)

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

  1. USE constraintsDB;
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:

  1. CREATE TABLE employeeInfo (
  2. empId int UNIQUE,
  3. empName varchar(30),
  4. empPhoneNum int
  5. );

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):

  1. CREATE TABLE racersInfo (
  2. racerId int,
  3. finish int,
  4. racerName varchar(30),
  5. CHECK (finish > 0)
  6. );

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:

  1. CREATE TABLE newRacersInfo (
  2. racerId int,
  3. finish int,
  4. racerName varchar(30),
  5. CONSTRAINT noNegativeFinish
  6. CHECK (finish >= 1)
  7. );

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 TABLE statement, which returns the entire CREATE TABLE statement that created the named table:
  1. SHOW CREATE TABLE table_name;
  • PostgreSQL: The PostgreSQL client psql has a number of options you can use to reveal information about a given table. The \d option returns metadata of the named table:
  1. \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:

  1. 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:


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:



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.

Thanks for learning with the DigitalOcean Community. Check out our offerings for compute, storage, networking, and managed databases.

Learn more about us

Tutorial Series: How To Use SQL

Series Description

Structured Query Language — commonly known as SQL — is a language used to define, control, manipulate, and query data held in a relational database. SQL has been widely adopted since it was first developed in the 1970s, and today it’s the predominant language used to manage relational database management systems.

Ideal for managing structured data (data that can fit neatly into an existing data model), SQL is an essential tool for developers and system administrators in a wide variety of contexts. Also, because of its maturity and prevalence, candidates with SQL experience are highly sought after for jobs across a number of industries.

This series is intended to help you get started with using SQL. It includes a mix of conceptual articles and tutorials which provide introductions to various SQL concepts and practices. You can also use the entries in this series for reference while you continue to hone your skills with SQL.

Note: Please be aware that the tutorials in this series use MySQL in examples, but 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.

About the authors
Default avatar

Manager, Developer Education

Technical Writer @ DigitalOcean

Still looking for an answer?

Ask a questionSearch for more help

Was this helpful?
Leave a comment

This textbox defaults to using Markdown to format your answer.

You can type !ref in this text area to quickly search our full set of tutorials, documentation & marketplace offerings and insert the link!

Try DigitalOcean for free

Click below to sign up and get $200 of credit to try our products over 60 days!

Sign up

Join the Tech Talk
Success! Thank you! Please check your email for further details.

Please complete your information!

Get our biweekly newsletter

Sign up for Infrastructure as a Newsletter.

Hollie's Hub for Good

Working on improving health and education, reducing inequality, and spurring economic growth? We'd like to help.

Become a contributor

Get paid to write technical tutorials and select a tech-focused charity to receive a matching donation.

Welcome to the developer cloud

DigitalOcean makes it simple to launch in the cloud and scale up as you grow — whether you're running one virtual machine or ten thousand.

Learn more
DigitalOcean Cloud Control Panel