How to Use the CockroachDB Foreign Key Constrain
Introduction
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.
Prerequisites
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
A 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:
The
FOREIGN KEY
constraint must use the data type of the column that they reference.The column can only belong to one foreign key constraint.
The
FOREIGN KEY
constraint accepts null values for single-column foreign keys.Null values are accepted in multiple-column composite foreign keys under certain conditions. If
MATCH FULL
is specified, the write operation must contain null values for all of the foreign key columns. IfMATCH SIMPLE
is 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:
1 2 3 4 5 | 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:
1 | cockroach SQL --insecure |
Once you access the shell, you can create a database and a table that can be used in the examples:
1 | CREATE DATABASE test; |
Use the USE
command followed by the database name to enter the specified database after creating it.
The syntax for creating a table is:
1 | CREATE TABLE TABLE_NAME(COLUMN_NAME + DATATYPE + CONSTRAINT [OPTIONAL]); |
The command SHOW TABLES
will display the list of tables that exist in the database:
1 2 3 4 5 | table_name +------------+ name balance (2 rows) |
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:
1 | 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 balance
.
Let’s start by inserting a record into name
:
1 2 3 4 5 | INSERT INTO name VALUES(165, 'Sheryl'), (875, 'Levy'), (629, 'Lucy'), (654, 'Erza'), (235, 'Juvia'); |
Next, we’ll insert data into the balance
table:
1 | 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
statement:
1 | INSERT INTO balance VALUES(2, 123, 5524.71); |
The message will read:
1 | pq: foreign key violation: value [123] 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:
1 | INSERT INTO balance VALUES(2, 654, 7532.54), (3, 629, 8941.23); |
Use the SELECT * FROM balance
to see the current contents of the table:
1 2 3 4 5 | 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 DELETE
or 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:
1 | CREATE TABLE first_tbl(id INT PRIMARY KEY); |
Then we’ll create the referencing table:
1 | 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:
1 | INSERT INTO first_tbl VALUES(123), (456), (789); |
We’ll also need to insert some records in the referencing table:
1 | 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:
1 | UPDATE c SET id = 321 WHERE id = 123; |
Use the SELECT * FROM first_tbl
to view the contents of the table and see if the UPDATE
was successful:
1 2 3 4 5 | id +-----+ 321 456 789 |
We can also use a SELECT
to view the contents of the referencing table:
1 2 3 4 5 | 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
action:
1 | DELETE FROM first_tbl WHERE id = 321; |
Like the 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’.
Conclusion
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