Foreign keys

This topic describes foreign keys in Spanner and how you can use them to enforce referential integrity in your database solution.

Overview

Foreign keys allow you to define relationships between tables. Spanner ensures the data integrity of these relationships is maintained.

Imagine you are the lead developer for an e-commerce business. You are designing a database to process customer orders. The database needs to store information about each order, customer, and product. Figure 1 illustrates the basic database structure for the application.

Figure 1. Diagram of an order processing database

You define a Customers table to store customer information, an Orders table to keep track of all orders made, and a Products table to store information about each product that customers can order.

Figure 1 also shows links between the tables that map to the following real- world relationships:

• An order was placed by a customer

• An order was placed for a product

You decide your database should enforce the following rules to make sure orders in our system are valid.

• You can't create an order for a customer that doesn't exist.

• A customer can't place an order for a product you don't carry.

When we enforce these rules, or constraints, we say that we are maintaining the referential integrity of our data. When a database maintains referential integrity, all attempts to add invalid data, which would result in invalid links or references between data, will fail. Referential integrity prevents user errors. Spanner enforces referential integrity through foreign keys.

Enforce referential integrity with foreign keys

Let's look at our order processing example again, with more detail added to the design as shown in Figure 2.

Figure 2. Diagram of our database schema with foreign keys

The design now shows column names and types in each table. The Orders table also defines two foreign key relationships. FK_CustomerOrder ensures that all rows in Orders have a valid CustomerID. The FK_ProductOrder foreign key ensures that all ProductID values in the Orders table are valid. The following table maps these constraints back to the real-world rules we want to enforce.

Foreign Key Name	Constraint	Real-world description
FK_CustomerOrder	Ensures that all rows in Orders have a valid CustomerID	An order was placed by a valid customer
FK_ProductOrder	Ensures that all rows in Orders have a valid ProductID	An order was placed for a valid product

Spanner fails any transaction that attempts to insert or update a row in the Orders table that has a CustomerID or ProductID not found in the Customers and Products tables. It also fails transactions that attempt to update or delete rows in the Customers and Products tables that would invalidate the ids in the Orders table. For more details about how Spanner validates constraints, refer to the Transaction constraint validation below.

Define foreign keys

Foreign keys are created and removed from your Spanner database using DDL. Foreign keys are added to a new table with the CREATE TABLE statement. Similarly, you can add a foreign key to, or remove from, an existing table with the ALTER TABLE statement. The following is an example of creating a new table with a foreign key.

GoogleSQL

CREATE TABLE Orders (
  OrderID INT64 NOT NULL,
  CustomerID INT64 NOT NULL,
  Quantity INT64 NOT NULL,
  ProductID INT64 NOT NULL,
  CONSTRAINT FK_CustomerOrder FOREIGN KEY (CustomerID) REFERENCES Customers (CustomerID)
) PRIMARY KEY (OrderID);

PostgreSQL

CREATE TABLE Orders (
  OrderID BIGINT NOT NULL,
  CustomerID BIGINT NOT NULL,
  Quantity BIGINT NOT NULL,
  ProductID BIGINT NOT NULL,
  CONSTRAINT FK_CustomerOrder FOREIGN KEY (CustomerID) REFERENCES Customers (CustomerID),
  PRIMARY KEY (OrderID)
);

For more examples of how to create and manage foreign keys, see Create and manage foreign key relationships.

Note: You can also add and remove foreign keys using the Google Cloud console. Select edit as text from the table creation interface and enter the DDL to manage foreign keys for your table.

The following is a list of characteristics of foreign keys in Spanner.

• The table that defines the foreign key is the referencing table and foreign key columns are the referencing columns.

• The foreign key references the referenced columns of the referenced table.

• As in the example above, you can name each foreign key constraint. If you don't specify a name, Spanner generates a name automatically. The generated name is queryable from Spanner's INFORMATION_SCHEMA. Constraint names are scoped to the schema, along with the names for tables and indexes, for example, and must be unique within the schema.

• The number of referencing and referenced columns must be the same. Order is significant. The first referencing column refers to the first referenced column, the second to the second, etc.

• A referencing column and its referenced counterpart must be the same type. The columns must also be indexable.

• Foreign keys cannot be created on columns with the allow_commit_timestamp=true option.

• Array columns are not supported.

• JSON columns are not supported.

• A foreign key may reference columns of the same table (a "self-referencing" foreign key). An example is an Employee table with a ManagerId column that references the table's EmployeeId column.

• Foreign keys may also form circular relationships between tables where two tables reference each other, either directly or indirectly. The referenced table must exist before creating a foreign key, so at least one of the foreign keys must be added using the ALTER TABLE statement.

• The referenced keys must be unique. Spanner uses the PRIMARY KEY of the referenced table if the foreign key's referenced columns match the referenced table's primary key columns. If Spanner cannot use the referenced table's primary key, it creates a UNIQUE NULL_FILTERED INDEX over the referenced columns.

• Spanner may also be able to use the primary key of the referencing table, although this is less common. If not, Spanner creates a NULL_FILTERED INDEX over the referencing columns.

• Foreign keys do not use secondary indexes you have created; they create their own backing indexes. These backing indexes are usable in query evaluations, including in explicit force_index directives. The name of the backing indexes are queryable from Spanner's INFORMATION_SCHEMA. For more information, see Backing indexes.

Foreign key actions

Foreign key actions let you control what happens to the constrained column when the column it's referencing is deleted or updated. Spanner supports the use of the ON DELETE CASCADE action. With the foreign key ON DELETE CASCADE action, when you delete a row that contains a referenced foreign key, all rows that reference that key are also deleted in the same transaction.

You can add a foreign key with an action when you create your database using DDL. Use the CREATE TABLE statement to add foreign keys with an action to a new table. Similarly, you can use the ALTER TABLE statement to add a foreign key action to an existing table or to remove a foreign key action. The following is an example of how to create a new table with a foreign key action.

GoogleSQL

CREATE TABLE Customers (
  CustomerId INT64 NOT NULL,
  CustomerName STRING(MAX) NOT NULL,
) PRIMARY KEY(CustomerId);

CREATE TABLE ShoppingCarts (
  CartId INT64 NOT NULL,
  CustomerId INT64 NOT NULL,
  CustomerName STRING(MAX) NOT NULL,
  CONSTRAINT FKShoppingCartsCustomers FOREIGN KEY(CustomerId, CustomerName)
    REFERENCES Customers(CustomerId, CustomerName) ON DELETE CASCADE,
) PRIMARY KEY(CartId);

PostgreSQL

CREATE TABLE Customers (
  CustomerId bigint NOT NULL,
  CustomerName character varying(1024) NOT NULL,
  PRIMARY KEY(CustomerId)
);

CREATE TABLE ShoppingCarts (
  CartId bigint NOT NULL,
  CustomerId bigint NOT NULL,
  CustomerName character varying(1024) NOT NULL,
  PRIMARY KEY(CartId),
  CONSTRAINT fkshoppingcartscustomers FOREIGN KEY (CustomerId, CustomerName)
    REFERENCES Customers(CustomerId, CustomerName) ON DELETE CASCADE
);

The following is a list of characteristics of foreign key actions in Spanner.

• Foreign key actions are either ON DELETE CASCADE or ON DELETE NO ACTION.

• You can query the INFORMATION_SCHEMA to find foreign key constraints that have an action.

• Adding a foreign key action on an existing foreign key constraint isn't supported. You need to add a new foreign key constraint with an action.

Long-running schema changes

Adding a foreign key to an existing table, or creating a new table with a foreign key, can lead to long-running operations. In the case of a new table, the table is not writable until the long-running operation is completed.

For a new table with a foreign key, Spanner needs to backfill the referenced indexes as needed for each foreign key.

For an existing table with a foreign key, Spanner needs to backfill the referencing and referenced indexes as needed. In addition, Spanner validates existing data in the table to ensure it complies with the foreign key's referential integrity constraint. The schema change fails if any data is invalid.

Adding a foreign key action on an existing constraint is not supported. We suggest that you do the following:

1. Add a new constraint with action.
2. Drop the older constraint without action.

This avoids a Long-running Alter Constraint Operation issue. After you create the new foreign key with the ON DELETE CASCADE action, the net effect of both the constraints is DELETE CASCADE. Dropping a constraint might lead to dropping the foreign key backing indexes if the indexes aren't in use in other foreign key constraints. Later, if the user adds the same foreign key constraint with action, it might require long-running operations, including backfilling indexes, validating unique index constraints, and validating foreign key referential constraints.

Note: If you don't drop the older foreign key constraint, there is overhead due to validating the extra foreign key constraints.

Either of the above schema changes can fail if the referenced index can't be created due to a UNIQUE constraint violation.

You can query INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS.SPANNER_STATE to check foreign key creation state.

Constraint validation for a transaction

Spanner validates foreign key constraints when a transaction is being committed, or when the effects of writes are being made visible to subsequent operations in the transaction.

A value inserted into the referencing column(s) is matched against the values of the referenced table and referenced columns. Rows with NULL referencing values are not checked, that is, you can add them to the referencing table.

Spanner validates all applicable foreign key referential constraints when attempting to update data via either DML statements or an API. All pending changes are rolled back if any constraints are invalid.

Validation occurs immediately after each DML statement. You must, for example, insert the referenced row before inserting its referencing rows. When using a mutation API, mutations are buffered until the transaction is committed. Foreign key validation is deferred until the transaction is committed. In this case it is permissible to insert the referencing rows first.

Each transaction is evaluated for modifications that affect foreign key constraints. These evaluations may require additional requests to the server. Backing indexes also require additional processing time to evaluate transaction modifications and to maintain the indexes. Additional storage is also required for each index.

Backing indexes

Foreign keys do not use user-created indexes. They create their own backing indexes.

Spanner can create up to two secondary backing indexes for each foreign key, one for the referencing columns, and a second for the referenced columns. However, a foreign key usually references the primary keys of the referenced table, so the second index on the referenced table is typically not needed.

The backing index for the referenced table is a UNIQUE NULL_FILTERED index; the creation of the foreign key fails if any existing data violates the index's uniqueness constraint. The backing index for the referencing table is NULL_FILTERED.

If two or more foreign keys require the same backing index, Spanner will create a single index for all of them. The backing indexes are dropped when the foreign keys using them are dropped. Users can't alter or drop the backing indexes.

Spanner uses each database's information schema to store metadata about backing indexes. Rows within INFORMATION_SCHEMA.INDEXES with a SPANNER_IS_MANAGED value of true describe backing indexes.

Outside of SQL queries that directly invoke the information schema, the Google Cloud console does not otherwise display any information about a database's backing indexes.

Long-running delete cascade action

When you delete a row from a referenced table, Spanner needs to delete all rows in the referencing tables that reference the deleted row. This can lead to a cascading effect, where a single delete operation can result in thousands of other delete operations. Adding a foreign key constraint with delete cascade action to a table or creating a table with foreign key constraints with delete cascade action can slow down delete operations.

Mutation limit exceeded for foreign key delete cascade

Deleting a large number of records using a foreign key delete cascade can impact performance. This is because each record that is deleted triggers the deletion of all of the records that are related to it that are using foreign keys. If the number of mutations in a transaction exceeds 40,000, the transaction fails.

If you need to delete a large number of records using a foreign key delete cascade, you should explicitly delete the rows from the child tables before deleting the row from the parent tables. This prevents the transaction from failing due to the mutation limit.

Comparison of foreign keys and table interleaving

Spanner's table interleaving is a good choice for many parent-child relationships where the child table's primary key includes the parent table's primary key columns. The co-location of child rows with their parent rows can significantly improve performance.

Foreign keys are a more general parent-child solution and address additional use cases. They are not limited to primary key columns, and tables can have multiple foreign key relationships, both as a parent in some relationships and a child in others. However, a foreign key relation does not imply co-location of the tables in the storage layer.

Let's look at an example, using the order processing schema we discussed earlier in this topic. Recall our Orders table was defined as follows:

Figure 3. Diagram of our database schema with foreign keys

The design in Figure 3 has some limitations. For example, each order can currently only contain one order item.

Let's imagine our customers tell us that they'd like to be able to order more than one product per order. We can enhance our design by introducing an OrderItems table that contains an entry for each product the customer ordered. We can introduce another foreign key to represent this new one-to-many relationship between Orders and OrderItems. But, we also know that a lot of the time we'll want to run queries across orders and their respective order items. Co-location of this data would boost performance, so we'll create the parent-child relationship using Spanner's table interleaving capability.

Here's how we define the OrderItems table, interleaved with Orders.

GoogleSQL

CREATE TABLE OrderItems (
  OrderID INT64 NOT NULL,
  ProductID INT64 NOT NULL,
  Quantity INT64 NOT NULL,
  FOREIGN KEY (ProductID) REFERENCES Products (ProductID)
) PRIMARY KEY (OrderID, ProductID),
  INTERLEAVE IN PARENT Orders ON DELETE CASCADE;

PostgreSQL

CREATE TABLE OrderItems (
  OrderID BIGINT NOT NULL,
  ProductID BIGINT NOT NULL,
  Quantity BIGINT NOT NULL,
  FOREIGN KEY (ProductID) REFERENCES Products (ProductID),
  PRIMARY KEY (OrderID, ProductID)
) INTERLEAVE IN PARENT Orders ON DELETE CASCADE;

Figure 4 is a visual representation of the updated database schema as a result of introducing this new table, OrderItems, interleaved with Orders. Here you can also see the one-to-many relationship between those two tables.

Figure 4. Addition of an interleaved OrderItems table

In this configuration, we can have multiple OrderItems entries in each order and the OrderItems entries for each order are interleaved, and therefore co- located with the orders. Physically interleaving Orders and OrderItems in this way can improve performance, effectively pre-joining the tables and allowing you to access related rows together while minimizing disk accesses. For example, Spanner can perform joins by primary key locally, minimizing disk access and network traffic.

If the number of mutations in a transaction exceeds 40,000, the transaction fails. Such large cascading deletes work well for tables with an "interleaved in parent" relationship, but not for tables with a foreign key relationship. If you have a foreign key relationship and you need to delete a large number of rows, you should explicitly delete the rows from the child tables first.

If you have a user table with a foreign key relationship to another table, and deleting a row from the referenced table triggers the deletion of millions of rows, you should design your schema with a delete cascade action with "interleaved in parent".

Note: Google recommends that you choose to represent parent-child relationships either as interleaved tables or as foreign keys, but not both. Using both is redundant and might consume additional storage and computing resources in order to verify constraints and maintain backing indexes.

Comparison table

The following table summarizes how foreign keys and table interleaving compare. You can use this information to decide what is right for your design.

Parent-child relationship type	Table Interleaving	Foreign Keys
Can use primary keys	Yes	Yes
Can use non-primary-key columns	No	Yes
Number of parents supported	0 .. 1	0 .. N
Stores parent and child data together	Yes	No
Supports cascade delete	Yes	Yes
Null matching mode	Passes if all referencing values are not distinct from the referenced values. Null values are not distinct from null values; null values are distinct from non-null values.	Passes if any referencing values are null. Passes if all referencing values are non-null, and the referenced table has a row with values equal to the referencing values. Fails if no matching row was found.
Enforcement Timing	Per operation when using the mutation API. Per statement when using DML.	Per transaction when using the mutation API. Per statement when using DML.
Can be removed easily	No. Table interleaving cannot be removed after it's created, unless you delete the whole child table.	Yes

What's next

• Learn about Creating and managing foreign key relationships.

• Learn more about the information schema.