H2数据库上的条件唯一索引 [英] Conditional Unique index on h2 database
本文介绍了H2数据库上的条件唯一索引的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有一个带有BIZ_ID列的SAMPLE_TABLE,当活动列不等于0时,该列应该是唯一的.
I have a SAMPLE_TABLE with a column BIZ_ID which should be unique when the column active is not equal to 0.
在oracle数据库上,索引如下:
On an oracle database the index looks like this:
CREATE UNIQUE INDEX ACTIVE_ONLY_IDX ON SAMPLE_TABLE (CASE "ACTIVE" WHEN 0 THEN NULL ELSE "BIZ_ID" END );
这个唯一索引在h2数据库上看起来如何?
How would this unique index look like on a h2 database?
推荐答案
在H2中,您可以使用具有唯一索引的计算列:
In H2, you could use a computed column that has a unique index:
create table test(
biz_id int,
active int,
biz_id_active int as
(case active when 0 then null else biz_id end)
unique
);
--works
insert into test(biz_id, active) values(1, 0);
insert into test(biz_id, active) values(1, 0);
insert into test(biz_id, active) values(2, 1);
--fails
insert into test(biz_id, active) values(2, 1);
这篇关于H2数据库上的条件唯一索引的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文