DEV Community

Rob Reid
Rob Reid

Posted on • Originally published at robreid.io

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`)
);
Enter fullscreen mode Exit fullscreen mode

...and updated as follows:

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

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'))
);
Enter fullscreen mode Exit fullscreen mode

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

SELECT constraint_name, details FROM [SHOW CONSTRAINTS FROM "person"];
Enter fullscreen mode Exit fullscreen mode
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))
Enter fullscreen mode Exit fullscreen mode

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"
Enter fullscreen mode Exit fullscreen mode

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'))
);
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

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"];
Enter fullscreen mode Exit fullscreen mode
constraint_name details
primary PRIMARY KEY (id ASC)
check_country_v2 CHECK ((country IN ('DE':::STRING, 'FR':::STRING, 'UK':::STRING, 'US':::STRING, 'ZW':::STRING)))

Discussion (0)