CockroachDB Enumerations

MySQL has a useful data type called ENUM which is an enumeration of values to validate against a given column.

An ENUM value when used, occupies 1 byte, rather than the full length of the string value.

ENUM values can be created as follows:

CREATE TABLE `person` (
	`id` BIGINT(20) NOT NULL AUTO_INCREMENT,
	`country` ENUM('DE', 'FR', 'UK', 'US') NOT NULL,
	PRIMARY KEY (`id`)
);

…and updated as follows:

ALTER TABLE `person`
MODIFY COLUMN `country`
	ENUM('DE', 'FR', 'UK', 'US', 'ZW')
NOT NULL AFTER `id`;

In CockroachDB, there is no ENUM type (although this is being discussed here and here). Happily, CockroachDB has the CHECK CONSTRAINT, which acheives the behaviour of an enumeration, minus the space-saving that a built-in ENUM type provides.

Adding CHECK CONSTRAINTS at CREATE-time can be done as follows:

CREATE TABLE "person" (
	"id" UUID NOT NULL DEFAULT uuid_v4()::UUID,
	"country" VARCHAR(2) NOT NULL,
	PRIMARY KEY ("id"),
	CONSTRAINT check_country CHECK ("country" IN ('DE', 'FR', 'UK', 'US'))
);

Let’s have a look at our freshly minted constraint:

SELECT constraint_name, details FROM [SHOW CONSTRAINTS FROM "person"];
constraint_name details
primary PRIMARY KEY (id ASC)
check_country_v1 CHECK ((country IN (‘DE’:::STRING, ‘FR’:::STRING, ‘UK’:::STRING, ‘US’:::STRING)))


To test that our constraint is working, let’s insert a valid value and an invalid value:

INSERT INTO "person" ("country") VALUES ('DE');
--> INSERT 1

INSERT INTO "person" ("country") VALUES ('AA');
--> pq: failed to satisfy CHECK constraint (country IN ('DE':::STRING, 'FR':::STRING, 'UK':::STRING, 'US':::STRING))

To update a CHECK CONSTRAINT atomically, use ADD CONSTRAINT and DROP CONSTRAINT within a transaction:

BEGIN;
ALTER TABLE "person" DROP CONSTRAINT check_country;
ALTER TABLE "person" ADD CONSTRAINT check_country CHECK ("country" IN ('DE', 'FR', 'UK', 'US', 'ZW'));
COMMIT;
--> pq: duplicate constraint name: "check_country"

UH OH!

Outside of a transaction, the DROP and ADD statements would have worked as expected. If you’re comfortable that the chance of missing a check between dropping and adding is minimal, running separately could work for you.

If on the other hand you need the atomicity of a transaction, you can name your CHECK CONSTRAINT such that it works within transactions. To do this, I version my constraints as follows:

CREATE TABLE "person" (
	"id" UUID NOT NULL DEFAULT uuid_v4()::UUID,
	"country" VARCHAR(2) NOT NULL,
	PRIMARY KEY ("id"),
	CONSTRAINT check_country_v1 CHECK ("country" IN ('DE', 'FR', 'UK', 'US'))
);

This time, we’re dropping one constraint and adding another:

BEGIN;
ALTER TABLE "person" DROP CONSTRAINT check_country_v1;
ALTER TABLE "person" ADD CONSTRAINT check_country_v2 CHECK ("country" IN ('DE', 'FR', 'UK', 'US', 'ZW'));
COMMIT;

Checking the constraint again reviews that our v1 constraint has been dropped and our v2 constraint has been added:

SELECT constraint_name, details FROM [SHOW CONSTRAINTS FROM "person"];
constraint_name details
primary PRIMARY KEY (id ASC)
check_country_v2 CHECK ((country IN (‘DE’:::STRING, ‘FR’:::STRING, ‘UK’:::STRING, ‘US’:::STRING, ‘ZW’:::STRING)))