Oracle有条件的唯一制约能加吗？

[scbb]

两个字段，
ABC,Status
当Status=1时，ABC不能重复。 即当Status<>1时，ABC可以重复。
这种Unique制约可以加吗？

[卖水果的net]

-- 这个要加 unique index 来实现，加 unique 约束不可以的

SQL> 
SQL> -- 当Status=1时，ABC不能重复。 即当Status<>1时，ABC可以重复。
SQL> create table test(ABC int, Status int);
Table created
SQL> create unique index uix_test  on test(decode(status,1,ABC,null));
Index created
SQL> insert into test(ABC, status) values(1,1);
1 row inserted
SQL> insert into test(ABC, status) values(1,1);  -- 失败
insert into test(ABC, status) values(1,1)
ORA-00001: 违反唯一约束条件 (ORACLE.UIX_TEST)
SQL> insert into test(ABC, status) values(1,0);
1 row inserted
SQL> insert into test(ABC, status) values(1,0);
1 row inserted
SQL> insert into test(ABC, status) values(1,NULL);
1 row inserted
SQL> insert into test(ABC, status) values(2,1);
1 row inserted
SQL> insert into test(ABC, status) values(2,2);
1 row inserted
SQL> insert into test(ABC, status) values(3,2);
1 row inserted
SQL> insert into test(ABC, status) values(3,2);
1 row inserted
SQL> select * from test;
                                    ABC                                  STATUS
--------------------------------------- ---------------------------------------
                                      1                                       1
                                      1                                       0
                                      1                                       0
                                      1 
                                      2                                       1
                                      2                                       2
                                      3                                       2
                                      3                                       2
8 rows selected
SQL> drop table test purge;
Table dropped

SQL> 

[scbb]

引用 1 楼 wmxcn2000 的回复:

-- 这个要加 unique index 来实现，加 unique 约束不可以的

SQL> 
SQL> -- 当Status=1时，ABC不能重复。 即当Status<>1时，ABC可以重复。
SQL> create table test(ABC int, Status int);
Table created
SQL> create unique index uix_test  on test(decode(status,1,ABC,null));
Index created
SQL> insert into test(ABC, status) values(1,1);
1 row inserted
SQL> insert into test(ABC, status) values(1,1);  -- 失败
insert into test(ABC, status) values(1,1)
ORA-00001: 违反唯一约束条件 (ORACLE.UIX_TEST)
SQL> insert into test(ABC, status) values(1,0);
1 row inserted
SQL> insert into test(ABC, status) values(1,0);
1 row inserted
SQL> insert into test(ABC, status) values(1,NULL);
1 row inserted
SQL> insert into test(ABC, status) values(2,1);
1 row inserted
SQL> insert into test(ABC, status) values(2,2);
1 row inserted
SQL> insert into test(ABC, status) values(3,2);
1 row inserted
SQL> insert into test(ABC, status) values(3,2);
1 row inserted
SQL> select * from test;
                                    ABC                                  STATUS
--------------------------------------- ---------------------------------------
                                      1                                       1
                                      1                                       0
                                      1                                       0
                                      1 
                                      2                                       1
                                      2                                       2
                                      3                                       2
                                      3                                       2
8 rows selected
SQL> drop table test purge;
Table dropped

SQL> 

谢谢，请问这样影响查询和Insert性能多吗？ 还有 insert into test(ABC, status) values(NULL, 2); insert into test(ABC, status) values(NULL, 2); insert into test(ABC, status) values(3, 2); 这样两次应该都不重复吧？

[卖水果的net]

引用 2 楼 scbb 的回复:

谢谢，请问这样影响查询和Insert性能多吗？ 还有 insert into test(ABC, status) values(NULL, 2); insert into test(ABC, status) values(NULL, 2); insert into test(ABC, status) values(3, 2); 这样两次应该都不重复吧？

请问这样影响查询和Insert性能多吗？ 对查询不会有影响的，对 insert 来说，只要加了新的索引，就会对 insert 有影响； 这样两次应该都不重复吧？ 这个要看，你要业务上怎么规定了； 如果允许出现这样的数据，也是可以实现的；

[scbb]

引用 3 楼 wmxcn2000 的回复:

[quote=引用 2 楼 scbb 的回复:] 谢谢，请问这样影响查询和Insert性能多吗？ 还有 insert into test(ABC, status) values(NULL, 2); insert into test(ABC, status) values(NULL, 2); insert into test(ABC, status) values(3, 2); 这样两次应该都不重复吧？

请问这样影响查询和Insert性能多吗？ 对查询不会有影响的，对 insert 来说，只要加了新的索引，就会对 insert 有影响； 这样两次应该都不重复吧？ 这个要看，你要业务上怎么规定了； 如果允许出现这样的数据，也是可以实现的；[/quote] 业务上允许的，只要Status不为1，ABC插入什么都可以，不管重复还是NULL。请问这样怎么实现？

[卖水果的net]

引用 4 楼 scbb 的回复:

业务上允许的，只要Status不为1，ABC插入什么都可以，不管重复还是NULL。请问这样怎么实现？

1# 的语句，一点都不用改，就是实现的这个功能，你可以先跑跑看；

[scbb]

引用 3 楼 wmxcn2000 的回复:

[quote=引用 2 楼 scbb 的回复:] 谢谢，请问这样影响查询和Insert性能多吗？ 还有 insert into test(ABC, status) values(NULL, 2); insert into test(ABC, status) values(NULL, 2); insert into test(ABC, status) values(3, 2); 这样两次应该都不重复吧？

请问这样影响查询和Insert性能多吗？ 对查询不会有影响的，对 insert 来说，只要加了新的索引，就会对 insert 有影响； 这样两次应该都不重复吧？ 这个要看，你要业务上怎么规定了； 如果允许出现这样的数据，也是可以实现的；[/quote] 到公司自己试了，你提供的方法可以的。 学习到了decode是个好东西。