How To Use Constraints in SQL
Introduction
Constraints in SQL allow you to impose restrictions on what data can be added to specific columns within a table when designing a database. By applying constraints, SQL ensures that any attempt to insert data that doesn’t comply with the defined rules will fail. This is an essential feature for maintaining data integrity and accuracy.
Different SQL implementations may handle constraints in SQL in various ways. This guide provides a comprehensive overview of how to manage constraints, with MySQL examples illustrating the syntax used across many database management systems.
Prerequisites for Constraints in SQL
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.
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 in SQL
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 column-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 in SQL
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)
);
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:
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:
\d table_name
Managing Constraints in SQL
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. The commands should work on most databases, but remember that each SQL database has its own unique language. You should consult your DBMS’s official documentation for a more complete description of each command and their full sets of options.
