How to Use the CockroachDB Foreign Key Constrain

Written by Data Pilot
March 23, 2020
  • CockroachDB
Have a Database Problem? Speak with an Expert for Free
Get Started >>

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. If MATCH 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.

Rate 1 Star2 Stars3 Stars4 Stars5 Stars

Pilot the ObjectRocket Platform Free!

Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.

Get Started
Related Topics:
Additional Information:

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.