|
考虑一个包含可空列abc,def和xyz的表。如何执行:
! M) D, ^. E9 v" e; S; I/ V[ol]如果’abc’不为null,则将1(’abc’)设置为UNIQUE,并且
2 v+ J! I0 \ T" u' @' I) ?" Q- j* {如果’def’和’xyz’都不为null,则将2(’def,xyz’)设置为UNIQUE,并且
1 P* Z3 {% Q( E; d9 G上述集合中只有一个有效(包含非null)。
6 q; _+ \1 E* E b" J: Q[/ol]
0 q( {7 F9 X+ p0 s+ m: x" u: q这是我尝试过的代码:
y# R/ W! L# _, V* Y' B--# sqlite3 --version2 v5 m4 _; I8 n
-- 3.13.0 ....& G7 g7 a4 x0 T- D
DROP TABLE IF EXISTS try;
0 p. I6 Q. N- G( K" g/ uCREATE TABLE try(, M6 u" v, G7 U+ `7 o
-- 'abc' should be null, when 'def' and 'xyz' are not null.
2 b# [! C p* m8 r5 r7 Z7 s -- 'abc' should be not be null, when 'def' and 'xyz' are null. G) E6 k8 h! h1 X, a
-- 'def' and 'xyz' should be null, when 'abc' is not null.4 q8 ~3 R0 t) x0 L1 y
-- 'def' and 'xyz' should be not be null, when 'abc' is null.
$ T: f; A) e" ]6 Q abc TEXT,2 N1 f8 f n" L! W- `4 X
def TEXT,8 \$ s& Q! i7 |- E- a! m0 X& E, U o
xyz TEXT,3 f S( _4 a' C% L( \
CONSTRAINT combo_1 UNIQUE(abc),5 |3 l% c D1 n" d7 L( c
CONSTRAINT combo_2 UNIQUE(def, xyz)
9 r, W+ [7 [- ]9 S) C8 w);
3 t% W Y% a( q" [; sINSERT into try(abc) VALUES("a1"); -- should succeed+ `* ` b( f$ o% K+ k5 C1 x
INSERT into try(def, xyz) VALUES("d2", "x2"); -- should succeed
; l3 C, g) Q! ~7 j( V" C' @--' U! L ?5 i- U1 G$ A8 k) ?& X
INSERT into try(abc) VALUES(null); -- should not be allowed
8 X4 d9 E7 X1 l4 o JINSERT into try(abc, def) VALUES("a4", "d4"); -- should not be allowed+ A" j4 |! g5 U3 A: P
INSERT into try(abc, xyz) VALUES("a5", "x5"); -- should not be allowed2 S" M' A" g( b
INSERT into try(abc, def, xyz) VALUES("a6", "d6", "x6"); -- should not be allowed
6 O0 k5 g/ I. |4 \: K! a--
7 j/ u6 n x; Y( x! ^6 lINSERT into try(def) VALUES(null); -- should not be allowed
1 J( h q) F! t V7 T7 U2 v0 qINSERT into try(def) VALUES("d8"); -- should not be allowed$ q6 s# a6 ` W& A; @, M
--5 t# ?) E/ e( z! Q& Z- P2 h
INSERT into try(xyz) VALUES(null); -- should not be allowed6 Q9 S' p* m( N
INSERT into try(xyz) VALUES("x10"); -- should not be allowed
w7 [: e2 A& n9 ^--
2 y9 {# @3 d. W% K: i# YINSERT into try(def, xyz) VALUES(null, null); -- should not be allowed) S) S3 D# @/ A, g; g6 `
INSERT into try(def, xyz) VALUES("d12", null); -- should not be allowed
8 C" p2 Y0 H9 t4 s2 bINSERT into try(def, xyz) VALUES(null, "x13"); -- should not be allowed0 M( g: Q0 N* ^) M6 P
INSERT into try(abc, def, xyz) VALUES(null, null, null); -- should not be allowed8 u/ N5 h @ ~6 h0 ?5 ]* h
.headers ON
+ [/ c; f' v7 L1 U, e4 { bselect rowid,* from try;' {6 ^/ g6 H! @- c/ i. d; {
.echo on
3 a0 J9 T0 y9 i0 q7 m0 S# W--
4 Z- R- c; t2 v8 b5 ^0 `9 d% b-- Only these 2 rows should be present:& l7 t8 e4 A( `& B$ P
-- 1|a1||1 \% X, c$ F8 n
-- 2||d2|x2
% |- n% E' b1 Q6 E& [# c5 R' Y w--; g$ U& \2 R4 T, ]5 q; D
但是,当我只希望前两个成功时,所有14个插入都成功。
! |, s! X# g0 ]# c$ C: V1 j6 c X
, U$ {$ s$ C- U) W解决方案: |
|