Geek asks and answers

How to annotate a unique constraint with a WHERE clause in JPA

I need to use these unique restrictions in PostgreSQL

CREATE UNIQUE INDEX favorites_3col_uni_idx
ON favorites (user_id, menu_id, recipe_id)
WHERE menu_id IS NOT NULL;

CREATE UNIQUE INDEX favorites_2col_uni_idx
ON favorites (user_id, recipe_id)
WHERE menu_id IS NULL;

The first one I comment on in JPA:

@Table(uniqueConstraints= {
@UniqueConstraint(name="favorites_3col_uni_idx", columnNames = {"user_id", "menu_id", "recipe_id"})
})

But, can you annotate a second unique index in JPA?

thank.

+5
source share
1 answer

It seems you want to create partial indexes ( CREATE INDEX ... ON ... WHERE) using JPA constraint definitions.

They are specific enough for PostgreSQL and are not specified by JPA. To create them you need to use your own syntax. I do not think JPA offers any functions for defining an index.

, . CONSTRAINT constraint_name UNIQUE(columns) PostgreSQL. , PostgreSQL .

:

JPA , JPA, DDL, .. , JPA , . DDL EclipseLink; , Hibernate, OpenJPA - , EclipseLink.

JPA - , pg_catalog.pg_index. , EntityManager SQL CREATE UNIQUE INDEX. A @Startup @Singleton bean , EJB3.1. . PostgreSQL pg_catalog.pg_index. , , :

SELECT EXISTS(
    SELECT 1 
    FROM pg_index 
    WHERE indexrelid = 'public.indexname'::regclass
);

, , , , . pg_index , , . - indpred; , .

+2

More articles:


All Articles