当sqlite中的任何一列都可以为空时，如何在多列上设置唯一约束 [英] how to set unique constraint over multiple column when any one can be null in sqlite

问题描述

当sqlite中任何一列都可以为空时，如何在多列上设置唯一约束?

How do I set unique constraint over multiple column when any one can be null in sqlite?

例如我制作了 unique("col1","col2","col3") 并尝试将 insert into tablename values("abc","def",null) 两次插入两行.当第三列为空时，唯一约束不起作用.

e.g. I have made unique("col1","col2","col3") and tried with insert into tablename values("abc","def",null) twice it hase inserted both rows. The unique constraint is not working when third column is null.

推荐答案

在 sqlite 中，所有的 null 都是差异.我认为解决这个问题的最好方法是使用特殊的默认值设置列 c 不为空.然后使用默认值(例如0、'')来表示null.

In sqlite, all null are differences. I think the best way to solve this issue is to set column c not null with a special default value. Then use the default value (for example 0, '') to represent null.

您可以轻松地将此解决方案扩展到任何列

you can easily extend this solution to any columns

create table test (
    a text not null default "",
    b text not null default "",
    c text not null default ""
);

这篇关于当sqlite中的任何一列都可以为空时，如何在多列上设置唯一约束的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！