Unable to create unique index

Hibernate ORM
1

Hello Hibernate Team,

I’m using Hibernate 6.6.3 to create a PostgreSQL database and noticed some strange behaviours on index creation.

@Table(name = "accounts", indexes = {
        @Index(name = "test_unique_index", columnList = "id", unique = true),  
        @Index(name = "test_unique_index_2", columnList = "id, name", unique = true)
})

When I’m creating unique indexes, the resulting table is:

CREATE TABLE public.accounts
(
    id uuid NOT NULL,
    name character varying(255) COLLATE pg_catalog."default",
    CONSTRAINT test_unique_index PRIMARY KEY (id),
    CONSTRAINT test_unique_index_2 UNIQUE (id, name)
)
 
TABLESPACE pg_default;
 
ALTER TABLE public.accounts
    OWNER to postgres;

If I change them to non-unique indexes, they get created:

@Table(name = "accounts", indexes = {
        @Index(name = "test_non_unique_index", columnList = "id"),  
        @Index(name = "test_non_unique_index_2", columnList = "id, name")
})

results in:

CREATE TABLE public.accounts
(
    id uuid NOT NULL,
    name character varying(255) COLLATE pg_catalog."default",
    CONSTRAINT accounts_pkey PRIMARY KEY (id)
)
 
TABLESPACE pg_default;
 
ALTER TABLE public.accounts
    OWNER to postgres;
 
-- Index: test_non_unique_index
 
-- DROP INDEX public.test_non_unique_index;
 
CREATE INDEX test_non_unique_index
    ON public.accounts USING btree
    (id ASC NULLS LAST)
    TABLESPACE pg_default;
 
 
-- Index: test_non_unique_index_2
 
-- DROP INDEX public.test_non_unique_index_2;
 
CREATE INDEX test_non_unique_index_2
    ON public.accounts USING btree

After some research, I probably found the used method: Github

I find it weird that instead of an unique index, only the unique constraint is created. Is this the expected behaviour for unique indexes?

Thanks in advance and best regards,
Tobias Schaub

2

From PostgreSQL’s documentation page about unique indexes:

PostgreSQL automatically creates a unique index when a unique constraint or primary key is defined for a table. The index covers the columns that make up the primary key or unique constraint (a multicolumn index, if appropriate), and is the mechanism that enforces the constraint.