Adding unique contraint in rowstore table

1

I’m not able to create a unique key over the EMAIL field. I need the table to have the PK as the id and to be autoincrement and also the email should be unique.

CREATE ROWSTORE TABLE users (
id bigint NOT NULL AUTO_INCREMENT,
email varchar(140) NOT NULL,
CONSTRAINT pk_emp PRIMARY KEY (id),
CONSTRAINT emp_email_uk UNIQUE (email)
);

I tried with many shard keys but it’s not useful. Is there an option for this?

2

Today, all your unique keys must contain the shard key. So there is not an easy way to make the system enforce uniqueness on two separate single-column keys.

The id column is the shard key in your case because you made it the primary key.

One thing you can do is to have the app enforce uniqueness of the email column. You could do that by checking that nobody else has that email before you insert a record, or update the email column, for example. You could create a “key” on email, that is not unique, to make that run faster.

Another thing you could do is rely on auto_increment to give you unique IDs and shard on email with a UNIQUE constraint on Email, like this:

id bigint NOT NULL AUTO_INCREMENT,
email varchar(140) NOT NULL,
key(id),
shard(email),
CONSTRAINT emp_email_uk UNIQUE (email)
);

In the future we expect we’ll support multiple unique constraints via secondary indexes sharded differently than the primary. It’s a big feature so I can’t give an estimated date at this time.

1 Like