Understanding CockroachDB Constraints
Introduction
In CockroachDB, constraints are used to place certain restrictions on a column. These restrictions limit the values that can be inserted into the column. In this article, we’ll take a closer look at CockroachDB constraints and learn how to use them effectively.
Prerequisites
A few important prerequisites need to be in place before you attempt the examples in this article:
- You’ll need to have CockroachDB installed on your machine.
- In order to follow along with the examples, you’ll need to have a basic knowledge of CockroachDB. You can connect to your CockroachDB database using the
cockroach sqlcommand-line interface. - The
\ccommand that is commonly used in psql will not work in the Cockroach SQL interface&. Instead, you’ll need to execute theUSEcommand to connect to a database:
1 | USE test_db; |
CockroachDB Constraints
The following table provides a handy “cheat sheet” for some of the common constraints you can use when creating or altering a table in CockroachDB:
| CONSTRAINT | DESCRIPTION |
|---|---|
| PRIMARY KEY | The values must be unique for each row. Another table can use a primary key to create links by specifying it as their foreign key. |
| FOREIGN KEY | The values create relationships to the reference column. |
| NOT NULL | The values cannot be empty or NULL. |
| CHECK | The value must return ‘true’ or ‘false’. |
| UNIQUE | The values in each row must be different. |
| DEFAULT | The specified default value will be inserted to the column, if no value is provided in the INSERT statement. |
Using the Constraints
To add a constraint, you can specify it for a column when you are creating a table.
For our example, we’ll create a new table named employees. The table contains a column named employee_id that we will specify as our PRIMARY KEY. We will also specify the columns name, email and gender as NOT NULL:
1 2 3 4 5 6 | CREATE TABLE employees ( employee_id INT PRIMARY KEY, name VARCHAR NOT NULL, email VARCHAR NOT NULL, gender VARCHAR NOT NULL ); |
Add Constraint in CockroachDB
You can use the UNIQUE,CHECK or FOREIGN KEY constraints in CockroachDB by invoking the ADD CONSTRAINT SQL keyword in conjunction with ALTER TABLE.
For this example, we will add the UNIQUE constraint on the email column:
1 2 | ALTER TABLE employees ADD CONSTRAINT unique_email UNIQUE (email); |
Next, we’ll add the CHECK constraint on the gender column:
1 2 | ALTER TABLE employees ADD CONSTRAINT chk_gender CHECK (gender = 'male' OR gender = 'female'); |
We can use the DEFAULT constraint in conjunction with the ALTER COLUMN keywords as part of an ALTER TABLE SQL statement. For our next example, we’ll set the default value for the column gender to male:
1 2 | ALTER TABLE employees ALTER COLUMN gender SET DEFAULT 'male'; |
CockroachDB Rename Constraint Example
When you use the CONSTRAINT clause, you can specify the name of the constraint. The name of the constraint will be specified after the data type and before the constraint type. In the following example, we will specify a name for the constraint on the project_id column:
1 2 3 4 5 | CREATE TABLE projects ( project_id INT CONSTRAINT unique_id PRIMARY KEY, project VARCHAR NOT NULL, employee_id INT ); |
CockroachDB Foreign Key Constraint
For this example, we’ll create a relationship between the employees and projects tables using the FOREIGN KEY constraint:
1 2 3 | ALTER TABLE projects ADD CONSTRAINT fk_id FOREIGN KEY(employee_id) REFERENCES employees; |
‘SHOW CONSTRAINTS’ Statement in CockroachDB
To view the constraints on a table, you can use the SHOW CONSTRAINTS or SHOW CREATE statements. Let’s check the constraints on the tables we created:
1 2 3 4 5 6 7 | SHOW CONSTRAINTS FROM employees; TABLE_NAME | constraint_name | constraint_type | details | validated -------------+-----------------+-----------------+----------------------------------------------------+------------ employees | chk_gender | CHECK | CHECK (((gender = 'male') OR (gender = 'female'))) | TRUE employees | PRIMARY | PRIMARY KEY | PRIMARY KEY (employee_id ASC) | TRUE employees | unique_email | UNIQUE | UNIQUE (email ASC) | TRUE (3 ROWS) |
Here’s the SQL statement to SHOW CONSTRAINTS for the projects table:
1 2 3 4 5 6 | SHOW CONSTRAINTS FROM projects; TABLE_NAME | constraint_name | constraint_type | details | validated -------------+-----------------+-----------------+-------------------------------------------------------------+------------ projects | fk_id | FOREIGN KEY | FOREIGN KEY (employee_id) REFERENCES employees(employee_id) | TRUE projects | unique_id | PRIMARY KEY | PRIMARY KEY (project_id ASC) | TRUE (2 ROWS) |
Conclusion
Constraints offer a simple and effective way to control the data that can be inserted into a particular column in a table. If you want to unlock the full power of CockroachDB, it’s important to know how to create and manage these constraints. In this article, we provided an overview of CockroachDB constraints and showed how to create and view a table’s constraints. With our step-by-step instructions to guide you, you’ll be prepared to manage constraints on your own CockroachDB tables.
Pilot the ObjectRocket Platform Free!
Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.
Get Started