UNIQUE CONSTRAINT needs the ability to create HASH index to enforce uniqueness

[glennfawcett]

Is your feature request related to a problem? Please describe.
ATLER TABLE ADD CONSTRAINT ... UNIQUE...
Creates as UNIQUE INDEX to enforce the constraint. Sometimes this can lead to hot-spotting.

Describe the solution you'd like
Would like to have some SYNTAX to create a HASH index to enforce uniqueness.... something like:

ALTER TABLE ADD CONSTRAINT ... UNIQUE...... USING HASH

Describe alternatives you've considered
As a work-around you can create a UNIQUE HASH index which inadvertently creates a unique constraint based on the HASH index. So, there is a work-around, but it would be nice to have this in the CONSTRAINT syntax.

work-around used:

root@localhost:26257/defaultdb> show create z;
  table_name |                                                         create_statement
-------------+-----------------------------------------------------------------------------------------------------------------------------------
  z          | CREATE TABLE public.z (
             |     id INT8 NOT NULL DEFAULT unique_rowid(),
             |     id2 INT8 NULL,
             |     crdb_internal_id2_shard_16 INT8 NOT VISIBLE NOT NULL AS (mod(fnv32(crdb_internal.datums_to_bytes(id2)), 16:::INT8)) VIRTUAL,
             |     CONSTRAINT z_pkey PRIMARY KEY (id ASC),
             |     UNIQUE INDEX id2_hash_uniq (id2 ASC) USING HASH WITH (bucket_count=16)
             | )
(1 row)

root@localhost:26257/defaultdb> show constraints from z;
  table_name |         constraint_name          | constraint_type |                                            details                                             | validated
-------------+----------------------------------+-----------------+------------------------------------------------------------------------------------------------+------------
  z          | check_crdb_internal_id2_shard_16 | CHECK           | CHECK ((crdb_internal_id2_shard_16 IN (0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15))) |     t
  z          | id2_hash_uniq                    | UNIQUE          | UNIQUE (id2 ASC)                                                                               |     t
  z          | z_pkey                           | PRIMARY KEY     | PRIMARY KEY (id ASC)                                                                           |     t
(3 rows)


Time: 13ms total (execution 13ms / network 0ms)

root@localhost:26257/defaultdb> insert into z values (1,1);
INSERT 0 1


Time: 6ms total (execution 6ms / network 0ms)

root@localhost:26257/defaultdb> insert into z values (2,1);
ERROR: duplicate key value violates unique constraint "id2_hash_uniq"
SQLSTATE: 23505
DETAIL: Key (id2)=(1) already exists.
CONSTRAINT: id2_hash_uniq

Jira issue: CRDB-30022

[rafiss]

@glennfawcett Is the workaround to use a unique index sufficient, or is there a higher priority reason that we need the ALTER TABLE ADD CONSTRAINT ... UNIQUE...... USING HASH syntax?