This page describes how to manage foreign key relationships in your database.
Foreign keys allow you to define relationships between tables. Cloud Spanner ensures the referential integrity between these tables is maintained. The following diagram illustrates a simple database schema that we'll use in this guide.
Figure 1. Diagram of an order processing database schema
There are three tables in the schema shown in figure 1:
- The
Customers
table records the names of each customer. - The
Orders
tables keeps track of all orders made. - The
Products
table stores the product information for every product.
There are two foreign key relationships between these tables:
A foreign key relationship is defined between the
Orders
table and theCustomers
table to ensure that an order can't be created unless there is a corresponding customer.A foreign key relationship between the
Orders
table and theProducts
table ensures that an order can't be created for a product that doesn't exist.
With this schema as a reference, let's look at the Data Definition Language (DDL) statements you can use to manage these constraints in your database.
Add a foreign key when creating a new table
Let's assume we've already created the Customers
table in our simple product
ordering database. We need an Orders
table to store information about the
orders that customers make. To ensure all orders are valid, we don't want to
allow the system to insert rows into the Orders table that do not have a
matching entry in our Customers
table.
Here's the CREATE TABLE
DDL statement for the Orders
table that includes the
foreign key constraint referencing the Customers
table.
Google Standard SQL
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)
);
The preceding statement contains a CONSTRAINT
clause that we can describe as follows:
Use of the
CONSTRAINT
syntax enables you to name a constraint, making it easier to drop using the name you've chosen.The constraint has the name
FK_CustomerOrder
. Constraint names are scoped to the schema and must be unique within the schema.The
Orders
table, on which we define the constraint, is called the referencing table. TheCustomers
table is the referenced table.The referencing column in the referencing table is
CustomerID
. It references theCustomerID
field in theCustomers
table. If someone tries to insert a row intoOrders
with aCustomerID
that does not exist inCustomers
, the insert will fail.
The following snippet shows an alternative table creation statement. Here, the foreign key constraint is defined without a name. When you use this syntax, Cloud Spanner generates a name for you. To discover the names of all foreign keys, refer to View properties of a foreign key relationship.
Google Standard SQL
CREATE TABLE Orders (
OrderID INT64 NOT NULL,
CustomerID INT64 NOT NULL,
ProductID INT64 NOT NULL,
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,
FOREIGN KEY (CustomerID) REFERENCES Customers (CustomerID),
PRIMARY KEY (OrderID)
);
Add a foreign key to an existing table
We also want to make sure that orders can only be created for products that
exist. We'll use ALTER TABLE
to add another foreign key constraint to the
orders table as follows:
ALTER TABLE Orders
ADD CONSTRAINT FK_ProductOrder FOREIGN KEY (ProductID) REFERENCES Products (ProductID);
The referencing column in Orders
is ProductID
, and it references the ProductID
column in Products. Again, if you are fine with Cloud Spanner naming these
constraints for you, use the following syntax:
ALTER TABLE Orders
ADD FOREIGN KEY (ProductID) REFERENCES Products (ProductID);
Query data across foreign key relationships
SELECT * FROM Orders
INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID
INNER JOIN Products ON Orders.ProductsID = Products.ProductID;
Example of referential integrity
The main reason for adding foreign key relationships is so that Cloud Spanner can maintain the referential integrity of your data. If you modify data in a way that breaks a foreign key constraint, the update will fail with an error.
Consider the data in figure 2 below. Some customers have ordered products, as
shown in the orders table. Because of our foreign keys that are in place, we can
guarantee that the data that was inserted into the Orders
table has
referential integrity.
Figure 2. Sample data for in our ordering database.
Let's look at what happens when we attempt to modify the data in a way that would break referential integrity.
Add a row into the Orders
table with a CustomerID
value that does not exist in Customers
What happens if we try the following modification, given the sample data from the preceding diagram?
INSERT INTO Orders (OrderID, ProductID, Quantity, CustomerID)
VALUES (19, 337876, 4, 447);
In this case, we are attempting to insert a row into Orders
with a
CustomerID
(447) that doesn't exist in the Customers
table. If we allowed
this, we would have an invalid order in our system. However, the foreign key
constraint we defined from the Orders
table to the Customers
table protects
us, and the INSERT
fails with the following message, assuming the constraint
is called FK_CustomerOrder
.
Foreign key constraint `FK_CustomerOrder` is violated on table `Orders`. Cannot find referenced values in Customers(CustomerID).
Attempt to delete a row from the Customers
table when the customer is referenced in a foreign key constraint.
Let's imagine a situation where a customer unsubscribes from our online store. We want to remove the customer from our backend, so we attempt the following operation.
DELETE FROM Customers WHERE CustomerID = 721;
In this example, Cloud Spanner detects through the foreign key constraint
that there are still records in the Orders
table that reference the customer
row we are trying to delete. The following error is displayed in this case.
Foreign key constraint violation when deleting or updating referenced row(s): referencing row(s) found in table `Orders`.
To fix this issue, we would delete all referencing entries in Orders
first.
Another solution, had we defined the CustomerID
as NULLABLE
, would be to set
the CustomerID
field to NULL
to remove the reference. CASCADE DELETE
is not
supported on foreign keys.
View properties of a foreign key relationship
Cloud Spanner's INFORMATION_SCHEMA contains information about foreign keys and their backing indexes. The following are some examples of the questions you can answer by querying the INFORMATION SCHEMA.
For more information on backing indexes, see Foreign keys backing indexes.
What constraints are defined in my database?
SELECT tc.CONSTRAINT_NAME, tc.TABLE_NAME, tc.CONSTRAINT_TYPE
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS as tc
WHERE tc.CONSTRAINT_TYPE = 'FOREIGN KEY';
What foreign keys are defined in my database?
SELECT rc.CONSTRAINT_NAME, rc.UNIQUE_CONSTRAINT_NAME, rc.SPANNER_STATE
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS as rc;
Which indexes are secondary indexes for foreign keys, also known as backing indexes?
Foreign key backing indexes are managed by Cloud Spanner , so querying for
SPANNER_IS_MANAGED
on the INDEXES
view will return all backing indexes.
SELECT i.TABLE_NAME, i.INDEX_NAME, i.INDEX_TYPE, i.INDEX_STATE,
i.IS_UNIQUE, i.IS_NULL_FILTERED, i.SPANNER_IS_MANAGED
FROM INFORMATION_SCHEMA.INDEXES as i
WHERE SPANNER_IS_MANAGED = 'YES';
For more information, see Information Schema.
Remove a foreign key relationship
The following DDL drops a foreign key constraint from the Orders
table.
ALTER TABLE Orders
DROP CONSTRAINT FK_CustomerOrder;
The foreign key backing indexes are dropped automatically when the constraint itself is dropped.
Support for more complex foreign key relationships
Multiple columns
Foreign keys can reference multiple columns. The list of columns form a key that corresponds to a table's primary key or to a backing index. The referencing table contains foreign keys of the referenced table key.
In the following example, the foreign key definitions tell Cloud Spanner
that each SongName
value in the TopHits table must have a matching value in
the Songs table; and each SingerFirstName
and SingerLastName
pair of values
must have a matching FirstName
and LastName
pair of values in the Singers
table.
Google Standard SQL
CREATE TABLE TopHits (
Rank INT64 NOT NULL,
SongName STRING(MAX),
SingerFirstName STRING(MAX),
SingerLastName STRING(MAX),
-- Song names must either be NULL or have matching values in Songs.
FOREIGN KEY (SongName) REFERENCES Songs (SongName),
-- Singer names must either be NULL or have matching values in Singers.
FOREIGN KEY (SingerFirstName, SingerLastName)
REFERENCES Singers (FirstName, LastName)
) PRIMARY KEY (Rank);
PostgreSQL
CREATE TABLE TopHits (
Rank BIGINT NOT NULL,
SongName VARCHAR,
SingerFirstName VARCHAR,
SingerLastName VARCHAR,
-- Song names must either be NULL or have matching values in Songs.
FOREIGN KEY (SongName) REFERENCES Songs (SongName),
-- Singer names must either be NULL or have matching values in Singers.
FOREIGN KEY (SingerFirstName, SingerLastName)
REFERENCES Singers (FirstName, LastName),
PRIMARY KEY (Rank)
);
Circular references
Occasionally tables have circular dependencies, perhaps for legacy reasons or
due to denormalization. Cloud Spanner foreign keys permit circular references.
Since a referenced table must exist before a foreign key can reference it, one of
the foreign keys must be added with an ALTER TABLE
statement. Here's an example
- Create TableA, without a foreign key
- Create TableB with a foreign key constraint on TableA.
- Use
ALTER TABLE
on TableA to create a foreign key reference to TableB.
Self-referencing tables
One special type of circular reference is a table that defines a foreign key that references the same table. For example, the following snippet shows a foreign key to enforce that an employee's ManagerId is also an employee.
Google Standard SQL
CREATE TABLE Employees (
EmployeeId INT64 NOT NULL,
EmployeeName STRING(MAX) NOT NULL,
ManagerId INT64,
FOREIGN KEY (ManagerId) REFERENCES Employees (EmployeeId)
) PRIMARY KEY (EmployeeId);
PostgreSQL
CREATE TABLE Employees (
EmployeeId BIGINT NOT NULL,
EmployeeName VARCHAR NOT NULL,
ManagerId BIGINT,
FOREIGN KEY (ManagerId) REFERENCES Employees (EmployeeId),
PRIMARY KEY (EmployeeId)
);
What's next
Learn more about foreign keys support in Cloud Spanner.
Learn more about Cloud Spanner's INFORMATION SCHEMA.