CrateDB如何保留_id列默认值同时将email设为主键

Great question! Let’s break down how to achieve this in CrateDB—since by default, setting a primary key replaces the auto-generated random _id with the primary key value, we need to explicitly define the _id column to retain its random behavior while enforcing email uniqueness as a primary key.

Here’s the step-by-step solution:

1. Explicitly Define the _id Column with Random Generation #

CrateDB lets you override the default _id behavior by defining the column explicitly and using a generated value. We’ll use the random_string() function to generate a random string, and mark it as STORED so the value is persisted (not recalculated on every query).

2. Set email as the Primary Key #

Even with a custom _id, you can still set email as the primary key to enforce its uniqueness—this won’t overwrite the custom _id since we’ve explicitly defined it.

Full Table Creation Query #

CREATE TABLE users (
    _id STRING GENERATED ALWAYS AS (random_string(32)) STORED,
    firstname STRING,
    lastname STRING,
    email STRING PRIMARY KEY,
    address STRING
);

Key Details: #

• The random_string(32) generates a 32-character random string (you can adjust the number to change the length to fit your needs).
• STORED ensures the random _id value is saved to disk, making queries efficient instead of generating the value on the fly every time.
• Setting email STRING PRIMARY KEY guarantees that no duplicate email addresses are inserted, fully meeting your uniqueness requirement.
• Since we’ve explicitly defined _id, CrateDB won’t replace it with the primary key value—both columns will exist independently, with _id retaining its random nature.

Verification #

After creating the table, insert a test record to confirm the behavior:

INSERT INTO users (firstname, lastname, email, address)
VALUES ('John', 'Doe', 'john.doe@example.com', '123 Main St');

Query the table to check both columns have distinct values:

SELECT _id, email FROM users;

You’ll see a random string for _id and the email value as the primary key, exactly as you wanted.

内容的提问来源于stack exchange，提问作者user3681549