One of the most compelling reasons to use a SQL database is that it can enforce rules about data for you, ensuring that it can only enter the database if it meets your specifications.
But for it to do that effectively, you have to tell it what those rules are, so let’s talk about SQL constraints and how to add a constraint.
I already know the basics, skip the intro and take me to the how-to!
What is a SQL constraint?
SQL constraints are rules that allow data to be entered into a table only if it meets the predefined conditions. They are part of your database schema, the broader set of rules that governs your database.
One way to understand SQL constraints is to imagine a bar. In the United States, the legal drinking age is 21, so many bars have a constraint: you must be 21 to enter. Bouncers check your ID at the door, and only patrons 21 or older are permitted inside. SQL databases work the same way. You can set up constraints – rules for entry into your table – and the database will “bounce” any data that doesn’t meet those rules.
The ability to define SQL constraints is one of the big advantages of SQL databases. With a few lines of SQL, you can create rules and define the relationships between data in different tables in your database that the database will enforce automatically. This this means you don’t have write any code to try to define and enforce those rules within your application. (Not having to write that code can save you time and effort, and it also means you don’t have to worry about how future application updates, code refactors, etc. could impact your data.)
For example, adding a foreign key constraint linking orders and users tables could permit new orders to be entered into orders only if they are associated with an existing user. That way, you don’t have to worry about invalid orders finding their way into your database – any order that isn’t linked to a real user will be rejected.
Generally, we define the rules and constraints associated with each table when we’re first creating that table, as part of the CREATE TABLE statement. However, we also might want to add or drop constraints after a table is already created, and that’s where ALTER TABLE … ADD CONSTRAINT comes in.
What is ADD CONSTRAINT?
ADD CONSTRAINT is a SQL command that is used together with ALTER TABLE to add constraints (such as a primary key or foreign key) to an existing table in a SQL database.
The basic syntax of ADD CONSTRAINT is:
ALTER TABLE table_name ADD CONSTRAINT PRIMARY KEY (col1, col2);
The above command would add a primary key constraint to the table table_name.
We can do more than just add primary keys with ADD CONSTRAINT, but first we do need to cover an important thing to keep in mind about schema changes such as ADD CONSTRAINT for any database that’s in production.
A note on schema changes
The rules that govern what data can be entered into a table and how the data in various tables are related are called the database schema. Changing those rules is called a schema change.
As a next-generation distributed SQL database, CockroachDB supports online schema changes, meaning that you can change your database schema while the database is up and running, without having to take it offline.
Many other SQL database systems don’t have this capability, though, so you’ll need to plan any schema changes carefully to minimize downtime and reduce the impact that taking your database offline to execute the schema change has on users.
How to use ADD CONSTRAINT
Let’s take a look at a few examples of how to use ADD CONSTRAINT.
Note that we’re using CockroachDB syntax for the examples below, but the commands will be identical or very similar for almost any sort of SQL database, including PostgreSQL, MySQL, SQLite, SQL server, etc. In some cases, the syntax may be slightly different, so refer to your database’s documentation if you encounter issues, but the general principles are the same across all SQL databases.
Example: adding a primary key constraint
A primary key serves as the unique identifier for each row of data in a table. It may be composed of a single column or multiple columns from the table.
ALTER TABLE table_name ADD CONSTRAINT constraint_name PRIMARY KEY (col1, col2);
The statement above adds a primary key constraint to the table table_name, and defines that key as being the data in col1 and col2 of each row.
It also names that primary key constraint constraint_name – in some databases, this step may not be required.
(Note that in CockroachDB, a primary key is required to create a table, and a table can only have one primary key, so to perform the above operation we’d have to first drop the existing primary key. More on how to drop an existing constraint later in this article.)
Example: adding a foreign key constraint
A foreign key links data in one table to data in another table, allowing data a row of data to be entered into table2 only if the values in a specified column or columns match values already entered into a row in table1. (For more details, check out our full article on foreign keys and why they’re useful).
ALTER TABLE orders ADD CONSTRAINT users_fk FOREIGN KEY (user_id) REFERENCES users (user_id);
The above command allows a new row to be added to the orders table only if the user ID associated with the order already exists in the users table. It also gives that foreign key constraint the name users_fk.
Note that when establishing foreign key relationships, we must consider what the impact of deleting or modifying data in one table would be on the tables that are connected to it. Thus, it’s generally good practice to run a statement like the one above with additional parameters such as ON DELETE CASCADE and ON UPDATE CASCADE to define how those interactions are handled. See our foreign keys article for more details on these parameters and how they work.
Example: adding a UNIQUE constraint
A UNIQUE constraint requires that every value in a column be distinct from all other values in the column (with the exception of NULL values). This type of SQL constraint is used to do things such as ensure that the same email address cannot be associated with two different user accounts:
ALTER TABLE users ADD CONSTRAINT email_unique UNIQUE (email);
The above command allows new rows to be added to the users table only if the value in the email column does not match any of the emails already in the table.
As with the previous examples, it also assigns a name to this constraint, email_unique.
Example: adding a CHECK constraint
A CHECK constraint evaluates a column or columns' value, and allows the value to be entered into a table only if that value evaluates to TRUE for a Boolean expression that’s defined as part of the statement. For example:
ALTER TABLE orders ADD CONSTRAINT check_revenue_positive CHECK (revenue >= 0);
The above command assesses each new row, and enters it into the orders table only if the value in the revenue column is greater than or equal to zero. As with previous examples, we’ve named this constraint check_revenue_positive.
Note that in CockroachDB, running an ALTER TABLE … ADD CONSTRAINT CHECK statement will run a background job to evaluate all existing data in the table against that Boolean expression, and the ADD CONSTRAINT statement will fail if any existing rows don’t evaluate to TRUE.
Example: dropping a constraint with DROP CONSTRAINT
If we want to remove a constraint from a table, we can run the following command:
ALTER TABLE table DROP CONSTRAINT constraint_name;
To drop a constraint, we need to specify its name. In CockroachDB, we can easily find the name of a specific constraint by running SHOW CONSTRAINTS FROM table_name;. This will return a table listing each constraint associated with table_name, including a column that lists the names of each constraint.
Example: replacing a SQL constraint
In some cases, we may want to drop a constraint and add a new one at the same time. For example, we might want to replace the primary key in a table. One way to accomplish this is to use a SQL transaction, like so:
BEGIN;
ALTER TABLE users DROP CONSTRAINT primary_key;
ALTER TABLE users ADD CONSTRAINT primary_key PRIMARY KEY (city, name, id);
COMMIT;
The above command is a transaction, so it will commit only when both statements between BEGIN and COMMIT have executed successfully. This allows us to drop the existing primary_key constraint and add a new one at the same time, so there’s no period of time where our table is without a primary key.
Note: CockroachDB also has a separate command to make this operation even easier with ALTER TABLE ... ALTER PRIMARY KEY. However, this command is not supported by some other types of SQL databases. If you’re not sure, using the transaction approach outlined above should work in pretty much any SQL database.
Example: renaming a constraint with RENAME CONSTRAINT
If we want to rename a constraint after it has been created, we can also do that using an ALTER TABLE statement:
ALTER TABLE users RENAME CONSTRAINT email_unique TO unique_email_address;
Note that in the above statement, we need to specify the existing constraint name before we can rename it. In CockroachDB, the existing constraint name can be looked up by running a SHOW CONSTRAINTS command. For example, we could look up all of the constraints associated with the users table by running SHOW CONSTRAINTS FROM users;.
Naming SQL constraints
In the examples above, we’ve added names for each constraint such as email_unique and check_revenue_positive. Some SQL databases do not require explicitly naming constraints in this manner, and will generate names for constraints automatically when they’re created.
However, it’s good practice to give your constraints clear, descriptive names even if adding a constraint name isn’t strictly required. While some SQL databases will generate default names for you, they’re not always clear.
For example, if you’re using Oracle, for example, you’re likely to get an auto-generated SQL constraint name that looks something like this: SYS_C0043245. Can you tell what that constraint does?
Other SQL databases have default naming schemes that are slightly more descriptive, but even so, it’s better to give SQL constraints names that you can understand. This makes working with your constraints easier as your database grows and new constraints are added, and it also assists in troubleshooting and error handling.
If an insert fails becuase it violates a constraint, for example, getting an error message that it has violated tablename_primary_key is going to be more helpful than learning that it has violated SYS_C0043245.
Learn more about constraints and dig into advanced uses in the CockroachDB documentation.
