如何在JPA中使用WHERE子句注释唯一约束 [英] How to annotate unique constraint with WHERE clause in JPA
问题描述
我需要在PostgreSQL中使用这些独特的约束
I need to use these unique constraints 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;
我在JPA中注释的第一个:
The first one I annotate in JPA:
@Table(uniqueConstraints= {
@UniqueConstraint(name="favorites_3col_uni_idx", columnNames = {"user_id", "menu_id", "recipe_id"})
})
但是，'可以在JPA中注释第二个唯一索引吗?
But, ¿it is possible to annotate in JPA the second unique index?
谢谢.
推荐答案
您似乎想创建局部索引(CREATE INDEX ... ON ... WHERE).
这些是PostgreSQL特有的，不是JPA指定的.您将需要使用本机语法来创建它们.我认为JPA不提供任何用于索引定义的功能.
These are fairly PostgreSQL specific, and aren't specified by JPA. You will need to use native syntax to create them. I don't believe JPA offers any features for index definition.
您不能为此使用唯一约束，因为唯一部分索引不是唯一约束.不能在PostgreSQL中使用CONSTRAINT constraint_name UNIQUE(columns)创建部分唯一索引. PostgreSQL为所有唯一约束创建唯一索引只是实现细节.
You cannot use a unique constraint for this purpose because unique partial indexes are not unique constraints. Partial unique indexes cannot be created with CONSTRAINT constraint_name UNIQUE(columns) in PostgreSQL. It's only an implementation detail that PostgreSQL creates a unique index for a unique constraint at all.
请参阅:
- Specifying an Index (Non-Unique Key) Using JPA
- JPA: defining an index column
某些JPA提供程序提供了特定于该JPA提供程序的扩展注释，这些扩展注释添加了用于运行本机DDL脚本，使用注释定义索引的功能等.由于您没有提到您使用的是哪个JPA提供程序，因此我无法告诉您更多信息.这是EclipseLink索引DDL的文档；如果您使用的是Hibernate，OpenJPA或EclipseLink以外的其他工具，则此将 不起作用 .
Some JPA providers offer extension annotations specific to that JPA provider that add features for running native DDL scripts, defining indexes with annoations, etc. Since you haven't mentioned which JPA provider you are using I can't tell you more. Here's the documentation for EclipseLink index DDL; this will not work if you are using Hibernate, OpenJPA, or something other than EclipseLink.
JPA标准解决方法是在启动期间通过查询pg_catalog.pg_index来检查那些索引的存在.如果找不到它们，请使用EntityManager本机查询发送适当的本机SQL CREATE UNIQUE INDEX命令.如果使用EJB3.1，则@Startup @Singleton bean对于此类任务很有用.有关pg_catalog.pg_index的结构，请参见PostgreSQL文档.要仅检查给定名称的索引是否存在，请运行:
A JPA standard workaround is to check for the presence of those indexes during startup by querying pg_catalog.pg_index. If you don't find them, use an EntityManager native query to send the appropriate native SQL CREATE UNIQUE INDEX commands. A @Startup @Singleton bean is useful for this sort of task if you're using EJB3.1. See the PostgreSQL documentation for the structure of pg_catalog.pg_index. To just check if an index of a given name exists, run:
SELECT EXISTS(
SELECT 1
FROM pg_index
WHERE indexrelid = 'public.indexname'::regclass
);
请注意，上面的查询不执行任何操作来验证它是否为您期望的索引，但是您可以通过一些其他检查来做到这一点.只需在创建索引后检查pg_index的内容，即可知道要测试的内容.我不建议尝试检查indpred的任何特定值.只需确保它不为空即可.
Note that the above query does nothing to verify it's the index you expect, but you can do that with some additional checks. Just examine the contents of pg_index after creating the index so you know what to test for. I don't recommend trying to check for any particular value of indpred; just make sure it isn't null.
这篇关于如何在JPA中使用WHERE子句注释唯一约束的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
