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 let’s do a quick review of what constraints are and why they’re useful. (If you’re already a SQL database pro, feel free to skip right to the examples.
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.
One way to understand this 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.
This is one of the primary advantages of SQL databases. It’s easy to create rules and define relationships for your data in a SQL table, and this prevents you from having to code and define all of those data rules manually in your application.
For example, adding a foreign key constraint to a table permits new rows to be entered only if the specified columns match data in another table. This allows you to define clear relationships between tables and ensure that inaccurate data isn’t entered into the database at all.
If, for example, you’ve got an orders table with a foreign key constraint linking to the users table, you can ensure that orders aren’t accepted unless they’re associated with an existing user.
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.
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 is 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. (We’re using CockroachDB syntax here, but the commands will be identical or very similar for almost any sort of SQL database).
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 – this is optional. We could run the statement above without constraint_name (as it is written earlier in this article) and it would execute correctly. A default name would be generated and assigned to the constraint.
(Note that in CockroachDB, a primary key is required to create a table, 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; again, this part is optional).
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 that above with 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.
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 can be 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, but this part of the command is optional.
Example: adding a CHECK constraint
A CHECK constraint evaluates a column or columns’ value, and allows the 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.
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 also named this constraint check_revenue_positive, but this part of the command is optional.
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;
Adding a custom constraint names is an optional step when creating constraints, but each constraint added to a table will have a default name generated if we skip that step. In other words, all constraints have names. If the user doesn’t choose to name them, the database does it automatically.
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: 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;.
Learn more about constraints and dig into advanced uses in the CockroachDB documentation.
