10

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?

Alex Robinson
  • 12,633
  • 2
  • 38
  • 55

1 Answers1

15

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

Alex Robinson
  • 12,633
  • 2
  • 38
  • 55
  • 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. – Turnkey May 27 '17 at 20:03