How to Use the CockroachDB Foreign Key Constrain
When you have tables in CockroachDB that make references to each other, it’s important to enforce referential integrity. In other words, if column value B in one table refers to column value A in another table, column value A needs to exist. A foreign key constraint can be used to make sure that referential integrity is maintained between these tables when data is deleted, inserted or updated. In this article, we’ll discuss the CockroachDB foreign key constraint and review some examples of its use.
In order to get the most out of this tutorial, you’ll need to have a CockroachDB cluster installed and working on your machine. You’ll need to have one node running so that you can follow along with examples that use the foreign key constraint. The command
cockroach version can be used to return and display the version of CockroachDB that is currently installed in your system.
Use the CockroachDB FOREIGN KEY constraint
FOREIGN KEY is a constraint used in a column of a table to match the value of another table within the specified database cluster. The sole purpose of the constraint is to enforce referential integrity, ensuring that the value of the column in the first table must exist in the second table.
Details when using the FOREIGN KEY constraint in CockroachDB
There are a few key points to keep in mind when using the CockroachDB FOREIGN KEY constraint:
FOREIGN KEYconstraint must use the data type of the column that they reference.
The column can only belong to one foreign key constraint.
FOREIGN KEYconstraint accepts null values for single-column foreign keys.
Null values are accepted in multiple-column composite foreign keys under certain conditions. If
MATCH FULLis specified, the write operation must contain null values for all of the foreign key columns. If
MATCH SIMPLEis specified instead, the null values are accepted if they are in at least one foreign key column.
The FOREIGN KEY constraint syntax
The syntax for creating a
FOREIGN KEY is:
CREATE TABLE second_table(
COLUMN_NAME + DATATYPE REFERENCES
first_table (COLUMN_NAME) CONSTRAINTS [OPTIONAL],
DEFINITION_COLUMN CONSTRAINTS [OPTIONAL])
Access the SQL shell in CockroachDB
You can use the command below to access the built-in SQL shell for CockroachDB:
cockroach SQL --insecure
Once you access the shell, you can create a database and a table that can be used in the examples:
CREATE DATABASE test;
USE command followed by the database name to enter the specified database after creating it.
The syntax for creating a table is:
CREATE TABLE TABLE_NAME(COLUMN_NAME + DATATYPE + CONSTRAINT [OPTIONAL]);
SHOW TABLES will display the list of tables that exist in the database:
At this point, we have a table called
name and a second table
balance in our database. We’ll insert some records into our tables to help us understand how the FOREIGN KEY constraint works.
The syntax for inserting a record in CockroachDB looks like the following:
INSERT INTO TABLE_NAME VALUES(VALUE);
The FOREIGN KEY constraint in CockroachDB with default actions
We’ll try to use the
INSERT statement in our tables with the default actions for the foreign key constraint.
The referenced table would be the
name table and the referencing table is
Let’s start by inserting a record into
INSERT INTO name VALUES(165, 'Sheryl'),
Next, we’ll insert data into the
INSERT INTO balance VALUES(1, 875, 7589.66);
NOTE: If you try to insert a record and the id of the referenced table is not found, you’ll receive an error message.
Here’s an example of an invalid id in an
INSERT INTO balance VALUES(2, 123, 5524.71);
The message will read:
pq: foreign key violation: value  not found in name@primary [id] (txn=10f80e92-db06-42d2-a37a-6128c6cf671f)
Similarly, if you try to update the value in
name with an id of
875, you’ll just get an error because of the default action that is being enabled. The
ON UPDATE NO ACTION default action will not modify a value if there are any references to the value being updated.
Let’s insert some records again and see what happens:
INSERT INTO balance VALUES(2, 654, 7532.54), (3, 629, 8941.23);
SELECT * FROM balance to see the current contents of the table:
id | accounts | balance
1 | 875 | 7589.66
2 | 654 | 7532.54
3 | 629 | 8941.23
If you want to delete a record with a referenced value, you’ll get an error message similar to the one we saw in the previous example. This is because of the default action
ON DELETE NO ACTION that exists on the foreign key constraint in CockroachDB.
NOTE: You can
UPDATE a value if references to it do not exist.
Using the FOREIGN KEY constraint with CASCADE in CockroachDB
Next, let’s try to use the
FOREIGN KEY constraint in CockroachDB with the
CASCADE action for delete and update operations.
We will create another table in CockroachDB, which will be the referenced table:
CREATE TABLE first_tbl(id INT PRIMARY KEY);
Then we’ll create the referencing table:
CREATE TABLE second_tbl(id INT PRIMARY KEY, u_id INT REFERENCES first_tbl(id) ON UPDATE CASCADE ON DELETE CASCADE);
We’ll need to insert some records into the referenced table:
INSERT INTO first_tbl VALUES(123), (456), (789);
We’ll also need to insert some records in the referencing table:
INSERT INTO second_tbl VALUES(1, 123), (2, 456), (3,789);
Now, lets try to
UPDATE the value of the referenced table
first_tbl and see what happens:
UPDATE c SET id = 321 WHERE id = 123;
SELECT * FROM first_tbl to view the contents of the table and see if the
UPDATE was successful:
We can also use a
SELECT to view the contents of the referencing table:
id | u_id
1 | 321
2 | 456
3 | 789
Notice that the
CASCADE option updates both the referenced and the referencing table to maintain referential integrity.
We can also observe similar behavior using the
DELETE FROM first_tbl WHERE id = 321;
UPDATE example, a
DELETE with the
CASCADE option will delete records from both the referenced and referencing table where the id has a value of ‘321’.
When you’re working with CockroachDB tables that reference one another, maintaining referential integrity between the tables is essential. Using the CockroachDB FOREIGN KEY constraint helps keep those references consistent and accurate between tables. In this article, we showed you how to create tables with a foreign key constraint, and we also showed you how foreign key constraints work when you attempt to insert, update or delete data in the affected tables. With these examples to use as a guide, you’ll have no trouble implementing foreign key constraints on your own CockroachDB tables.
Pilot the ObjectRocket Platform Free!
Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.Get Started