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.
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.
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.
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).
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
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.)
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
(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.
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
As with the previous examples, it also assigns a name to this constraint,
email_unique, but this part of the command is optional.
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
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.
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.
Foreign keys put the “relational” in “relational database” – they help define the relationship between tables. They …Read more
Relational databases are great for transactional workloads. But things can get messy when multiple transactions start …Read more
Let’s be real: while there’s nothing wrong with a nice GUI, doing things with the command line is just …Read more