GCP - Spanner Enum

👉 Overview

👀 What ?

Google Cloud Spanner, or simply Spanner, is a globally distributed, horizontally scalable, relational database service built on Google Cloud Platform (GCP). It provides high availability and strong consistency at a global scale. A key feature of Spanner is its support for Enum data type, which allows users to define a type that consists of a fixed set of constants.

🧐 Why ?

The importance of Spanner's Enum capability lies in the efficiency and data integrity it brings to database systems. Enum data type helps in preventing data anomalies and increases query performance. It is particularly useful when a column in the database is expected to have a limited set of possible values, such as days of the week, months of the year, or status of an order. Enum ensures that only a predefined set of values are stored in the column, thus reducing the possibility of invalid data entries.

⛏️ How ?

To use Enum in Spanner, you first need to define an Enum type with a list of string constants. For instance, you might define an Enum type for order status as follows: CREATE TYPE OrderStatus AS ENUM ('pending', 'processing', 'shipped', 'delivered'). Once the Enum type is defined, you can use it in the schema of your tables. For example: CREATE TABLE Orders (OrderId INT64, Status OrderStatus). When inserting data into the Orders table, you must use a value that exists in the OrderStatus Enum, otherwise, an error will be thrown.

⏳ When ?

Spanner began supporting the Enum data type in its schema definition language in October 2020, as part of its efforts to provide more flexible and efficient ways to model data.

⚙️ Technical Explanations

At a technical level, Enums in Spanner are implemented as user-defined types. They are stored as integers, but are exposed as strings in SQL. This means that while they offer the readability of strings, they have the storage efficiency and performance of integers. They are based on the principle of atomicity, meaning that changes to a single Enum value are done in a single, atomic transaction. This ensures consistency and integrity of data. Moreover, Spanner provides built-in functions to work with Enums, such as casting functions and operators for equality, inequality, and ordering.