Understanding CockroachDB Constraints

Have a Database Problem? Speak with an Expert for Free
Get Started >>

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 sql command-line interface.
  • The \c command that is commonly used in psql will not work in the Cockroach SQL interface&. Instead, you’ll need to execute the USE command 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:

CONSTRAINTDESCRIPTION
PRIMARY KEYThe 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 KEYThe values create relationships to the reference column.
NOT NULLThe values cannot be empty or NULL.
CHECKThe value must return ‘true’ or ‘false’.
UNIQUEThe values in each row must be different.
DEFAULTThe 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';

Screenshot of a Cockroachdb constraint create table and alter table SQL example

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)

Cockroachdb constraint tutorial to create an SQL table and SHOW CONSTRAINTS

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

Keep in the know!

Subscribe to our emails and we’ll let you know what’s going on at ObjectRocket. We hate spam and make it easy to unsubscribe.