Details regarding UNIQUE KEY vs PRIMARY KEY

Help
1

Hi guys,

I’m looking for updating my columnstore table to benefit from the new “PRIMARY KEY” feature but I’m a bit confused by the documentation (Creating a Columnstore Table · SingleStore Documentation).

Is the PRIMARY KEY(x) syntax just a shortcut to create a UNIQUE KEY(x) USING HASH or the implementation is different?

The documentation says:

PRIMARY KEY(<column name>) is created as SHARD(<column name>), UNIQUE KEY(<column name>) USING HASH

But in my table, I want the primary key column to be different than the shard key.

Can you confirm that I will benefit from the new “primary key” feature by just adding a: UNIQUE KEY(<column name>) USING HASH ?

Will the “ON DUPLICATE KEY UPDATE” work with the UNIQUE KEY(<column name>) USING HASH ?

Thanks

2

Uniqueness enforcement relies on sharding, so the unique key has to contain the shard key today. Since unique key’s can have only one column in 7.3, that means a unique key has to be the shard key.

Feel free to create a feature request or upvote one if it’s already there, for having multiple unique keys or indexes sharded differently than the primary. We are tracking this internally as a feature request for the future.

INSERT … ON DUPLICATE KEY UPDATE will work regardless of how you declare the unique key or primary key.

3

Thank you @hanson for the details!