1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
|
# name: test/sql/constraints/unique/test_unique_temp.test
# description: UNIQUE constraint on temporary tables
# group: [unique]
statement ok
SET default_null_order='nulls_first';
statement ok
CREATE TEMPORARY TABLE integers(i INTEGER, j INTEGER)
statement ok
CREATE UNIQUE INDEX uidx ON integers (i)
# insert unique values
statement ok
INSERT INTO integers VALUES (3, 4), (2, 5)
query II
SELECT * FROM integers
----
3 4
2 5
# insert a duplicate value as part of a chain of values, this should fail
statement error
INSERT INTO integers VALUES (6, 6), (3, 4);
----
statement ok
INSERT INTO integers VALUES (NULL, 6), (NULL, 7)
# but if we try to replace them like this it's going to fail
statement error
UPDATE integers SET i=77 WHERE i IS NULL
----
query II
SELECT * FROM integers ORDER BY i, j
----
NULL 6
NULL 7
2 5
3 4
# we can replace them like this though
statement ok
UPDATE integers SET i=77 WHERE i IS NULL AND j=6
query II
SELECT * FROM integers ORDER BY i, j
----
NULL 7
2 5
3 4
77 6
statement ok
INSERT INTO integers VALUES (NULL, 6), (NULL, 7)
statement ok
INSERT INTO integers VALUES (NULL, 6), (NULL, 7)
statement ok
INSERT INTO integers VALUES (NULL, 6), (NULL, 7)
statement ok
INSERT INTO integers VALUES (NULL, 6), (NULL, 7)
statement ok
INSERT INTO integers VALUES (NULL, 6), (NULL, 7)
statement ok
INSERT INTO integers VALUES (NULL, 6), (NULL, 7)
statement ok
INSERT INTO integers VALUES (NULL, 6), (NULL, 7)
statement ok
INSERT INTO integers VALUES (NULL, 6), (NULL, 7)
statement ok
INSERT INTO integers VALUES (NULL, 6), (NULL, 7)
statement ok
INSERT INTO integers VALUES (NULL, 6), (NULL, 7)
statement error
INSERT INTO integers VALUES (NULL, 6), (3, 7)
----
statement ok
DROP TABLE integers
|
