Auto-increment support in CockroachDB

Question

In MySQL, I can use AUTO INCREMENT to generate unique IDs for my application’s customers. How do I get similar functionality when using CockroachDB?

Vasomotor

Answer 1

Applications cannot use constructs like SEQUENCE or AUTO_INCREMENT and also expect horizontal scalability -- this is a general limitation of any distributed database. Instead, CockroachDB provides its own SERIAL type which generates increasing but not necessarily contiguous values.

For example, you would use: CREATE TABLE customers (id SERIAL PRIMARY KEY, name STRING); Then when you’re inserting values, you would use something like: INSERT INTO customers (name) VALUES ('Kira Randell') RETURNING id; This would return the randomly generated ID, which you’d be able to use elsewhere in your application

Vasomotor

Thanks, good answer. Actually SERIAL not generating contiguous values is not unusual as other SQL databases often leave gaps because of issues with caching Sequences or an insert that gets rolled back. – Mineraloid