1#include "catch.hpp"
2#include "test_helpers.hpp"
3
4using namespace duckdb;
5using namespace std;
6
7TEST_CASE("Single UNIQUE constraint", "[constraints]") {
8 unique_ptr<QueryResult> result;
9 DuckDB db(nullptr);
10 Connection con(db);
11
12 REQUIRE_NO_FAIL(con.Query("CREATE TABLE integers(i INTEGER UNIQUE, j INTEGER)"));
13
14 // insert unique values
15 REQUIRE_NO_FAIL(con.Query("INSERT INTO integers VALUES (3, 4), (2, 5)"));
16
17 result = con.Query("SELECT * FROM integers");
18 REQUIRE(CHECK_COLUMN(result, 0, {3, 2}));
19 REQUIRE(CHECK_COLUMN(result, 1, {4, 5}));
20
21 // insert a duplicate value as part of a chain of values, this should fail
22 REQUIRE_FAIL(con.Query("INSERT INTO integers VALUES (6, 6), (3, 4);"));
23
24 // unique constraints accept NULL values, unlike PRIMARY KEY columns
25 REQUIRE_NO_FAIL(con.Query("INSERT INTO integers VALUES (NULL, 6), (NULL, 7)"));
26
27 // but if we try to replace them like this it's going to fail
28 REQUIRE_FAIL(con.Query("UPDATE integers SET i=77 WHERE i IS NULL"));
29
30 result = con.Query("SELECT * FROM integers ORDER BY i, j");
31 REQUIRE(CHECK_COLUMN(result, 0, {Value(), Value(), 2, 3}));
32 REQUIRE(CHECK_COLUMN(result, 1, {6, 7, 5, 4}));
33
34 // we can replace them like this though
35 REQUIRE_NO_FAIL(con.Query("UPDATE integers SET i=77 WHERE i IS NULL AND j=6"));
36
37 result = con.Query("SELECT * FROM integers ORDER BY i, j");
38 REQUIRE(CHECK_COLUMN(result, 0, {Value(), 2, 3, 77}));
39 REQUIRE(CHECK_COLUMN(result, 1, {7, 5, 4, 6}));
40
41 for (idx_t i = 0; i < 10; i++) {
42 REQUIRE_NO_FAIL(con.Query("INSERT INTO integers VALUES (NULL, 6), (NULL, 7)"));
43 }
44 REQUIRE_FAIL(con.Query("INSERT INTO integers VALUES (NULL, 6), (3, 7)"));
45
46 REQUIRE_NO_FAIL(con.Query("DROP TABLE integers"));
47}
48
49TEST_CASE("UNIQUE constraint on temporary tables", "[constraints]") {
50 unique_ptr<QueryResult> result;
51 DuckDB db(nullptr);
52 Connection con(db);
53
54 REQUIRE_NO_FAIL(con.Query("CREATE TEMPORARY TABLE integers(i INTEGER, j INTEGER)"));
55 REQUIRE_NO_FAIL(con.Query("CREATE UNIQUE INDEX uidx ON integers (i) "));
56
57 // insert unique values
58 REQUIRE_NO_FAIL(con.Query("INSERT INTO integers VALUES (3, 4), (2, 5)"));
59
60 result = con.Query("SELECT * FROM integers");
61 REQUIRE(CHECK_COLUMN(result, 0, {3, 2}));
62 REQUIRE(CHECK_COLUMN(result, 1, {4, 5}));
63
64 // insert a duplicate value as part of a chain of values, this should fail
65 REQUIRE_FAIL(con.Query("INSERT INTO integers VALUES (6, 6), (3, 4);"));
66
67 // unique constraints accept NULL values, unlike PRIMARY KEY columns
68 REQUIRE_NO_FAIL(con.Query("INSERT INTO integers VALUES (NULL, 6), (NULL, 7)"));
69
70 // but if we try to replace them like this it's going to fail
71 REQUIRE_FAIL(con.Query("UPDATE integers SET i=77 WHERE i IS NULL"));
72
73 result = con.Query("SELECT * FROM integers ORDER BY i, j");
74 REQUIRE(CHECK_COLUMN(result, 0, {Value(), Value(), 2, 3}));
75 REQUIRE(CHECK_COLUMN(result, 1, {6, 7, 5, 4}));
76
77 // we can replace them like this though
78 REQUIRE_NO_FAIL(con.Query("UPDATE integers SET i=77 WHERE i IS NULL AND j=6"));
79
80 result = con.Query("SELECT * FROM integers ORDER BY i, j");
81 REQUIRE(CHECK_COLUMN(result, 0, {Value(), 2, 3, 77}));
82 REQUIRE(CHECK_COLUMN(result, 1, {7, 5, 4, 6}));
83
84 for (idx_t i = 0; i < 10; i++) {
85 REQUIRE_NO_FAIL(con.Query("INSERT INTO integers VALUES (NULL, 6), (NULL, 7)"));
86 }
87 REQUIRE_FAIL(con.Query("INSERT INTO integers VALUES (3, 4)"));
88
89 REQUIRE_NO_FAIL(con.Query("DROP TABLE integers"));
90}
91
92TEST_CASE("NULL values and a multi-column UNIQUE constraint", "[constraints]") {
93 unique_ptr<QueryResult> result;
94 DuckDB db(nullptr);
95 Connection con(db);
96
97 REQUIRE_NO_FAIL(con.Query("CREATE TEMPORARY TABLE integers(i INTEGER, j INTEGER)"));
98 REQUIRE_NO_FAIL(con.Query("CREATE UNIQUE INDEX uidx ON integers (i,j) "));
99
100 // insert unique values
101 REQUIRE_NO_FAIL(con.Query("INSERT INTO integers VALUES (3, 4), (2, 5)"));
102
103 result = con.Query("SELECT * FROM integers");
104 REQUIRE(CHECK_COLUMN(result, 0, {3, 2}));
105 REQUIRE(CHECK_COLUMN(result, 1, {4, 5}));
106
107 // insert a duplicate value as part of a chain of values, this should fail
108 REQUIRE_FAIL(con.Query("INSERT INTO integers VALUES (6, 6), (3, 4);"));
109
110 // unique constraints accept NULL values, unlike PRIMARY KEY columns
111 REQUIRE_NO_FAIL(con.Query("INSERT INTO integers VALUES (NULL, 6), (NULL, 6), (NULL, 7)"));
112
113 // but if we try to replace them like this it's going to fail
114 REQUIRE_FAIL(con.Query("UPDATE integers SET i=77 WHERE i IS NULL"));
115
116 result = con.Query("SELECT * FROM integers ORDER BY i, j");
117 REQUIRE(CHECK_COLUMN(result, 0, {Value(), Value(), Value(), 2, 3}));
118 REQUIRE(CHECK_COLUMN(result, 1, {6, 6, 7, 5, 4}));
119
120 // we can replace them like this though
121 REQUIRE_NO_FAIL(con.Query("UPDATE integers SET i=77 WHERE i IS NULL AND j=7"));
122
123 result = con.Query("SELECT * FROM integers ORDER BY i, j");
124 REQUIRE(CHECK_COLUMN(result, 0, {Value(), Value(), 2, 3, 77}));
125 REQUIRE(CHECK_COLUMN(result, 1, {6, 6, 5, 4, 7}));
126
127 for (idx_t i = 0; i < 10; i++) {
128 REQUIRE_NO_FAIL(con.Query("INSERT INTO integers VALUES (NULL, 6), (NULL, 7)"));
129 }
130 REQUIRE_FAIL(con.Query("INSERT INTO integers VALUES (3, 4)"));
131
132 REQUIRE_NO_FAIL(con.Query("DROP TABLE integers"));
133}
134
135TEST_CASE("UNIQUE constraint on temporary tables with Strings", "[constraints]") {
136 unique_ptr<QueryResult> result;
137 DuckDB db(nullptr);
138 Connection con(db);
139
140 REQUIRE_NO_FAIL(con.Query("CREATE TEMPORARY TABLE integers(i INTEGER, j VARCHAR)"));
141 REQUIRE_NO_FAIL(con.Query("CREATE UNIQUE INDEX \"uidx\" ON \"integers\" (\"j\") "));
142 REQUIRE_NO_FAIL(con.Query("INSERT INTO integers VALUES (3, '4'), (2, '5')"));
143
144 result = con.Query("SELECT * FROM integers");
145 REQUIRE(CHECK_COLUMN(result, 0, {3, 2}));
146 REQUIRE(CHECK_COLUMN(result, 1, {"4", "5"}));
147
148 // insert a duplicate value as part of a chain of values, this should fail
149 REQUIRE_FAIL(con.Query("INSERT INTO integers VALUES (6, '6'), (3, '4');"));
150
151 // unique constraints accept NULL values, unlike PRIMARY KEY columns
152 REQUIRE_NO_FAIL(con.Query("INSERT INTO integers VALUES (6,NULL), (7,NULL)"));
153
154 // but if we try to replace them like this it's going to fail
155 REQUIRE_FAIL(con.Query("UPDATE integers SET j='77' WHERE j IS NULL"));
156
157 result = con.Query("SELECT * FROM integers ORDER BY i, j");
158 REQUIRE(CHECK_COLUMN(result, 0, {2, 3, 6, 7}));
159 REQUIRE(CHECK_COLUMN(result, 1, {"5", "4", Value(), Value()}));
160
161 // we can replace them like this though
162 REQUIRE_NO_FAIL(con.Query("UPDATE integers SET j='7777777777777777777777777777' WHERE j IS NULL AND i=6"));
163 for (idx_t i = 0; i < 10; i++) {
164 REQUIRE_NO_FAIL(con.Query("INSERT INTO integers VALUES (6,NULL), (7,NULL)"));
165 }
166 REQUIRE_FAIL(con.Query("INSERT INTO integers VALUES (3, '4')"));
167 REQUIRE_FAIL(con.Query("INSERT INTO integers VALUES (3, '4')"));
168
169 REQUIRE_NO_FAIL(con.Query("DROP TABLE integers"));
170}
171
172TEST_CASE("UNIQUE constraint on temporary tables with duplicate data", "[constraints]") {
173 unique_ptr<QueryResult> result;
174 DuckDB db(nullptr);
175 Connection con(db);
176
177 REQUIRE_NO_FAIL(con.Query("CREATE TEMPORARY TABLE integers(i INTEGER, j VARCHAR)"));
178 REQUIRE_NO_FAIL(con.Query("INSERT INTO integers VALUES (3, '4'), (2, '4')"));
179
180 REQUIRE_FAIL(con.Query("CREATE UNIQUE INDEX uidx ON integers (j) "));
181
182 REQUIRE_NO_FAIL(con.Query("DROP TABLE integers"));
183}
184
185TEST_CASE("Multiple constraints", "[constraints]") {
186 unique_ptr<QueryResult> result;
187 DuckDB db(nullptr);
188 Connection con(db);
189
190 REQUIRE_NO_FAIL(con.Query("CREATE TABLE integers(i INTEGER PRIMARY KEY, j INTEGER UNIQUE)"));
191
192 // no constraints are violated
193 REQUIRE_NO_FAIL(con.Query("INSERT INTO integers VALUES (1, 1), (2, 2)"));
194 // only the second UNIQUE constraint is violated
195 REQUIRE_FAIL(con.Query("INSERT INTO integers VALUES (3, 3), (4, 1)"));
196 // no constraints are violated
197 REQUIRE_NO_FAIL(con.Query("INSERT INTO integers VALUES (3, 3), (4, 4)"));
198 // insert many values with a unique constraint violation at the end
199 REQUIRE_FAIL(con.Query(
200 "INSERT INTO integers VALUES (5, 5), (6, 6), (7, 7), (8, 8), (9, 9), (10, 10), (11, 11), (12, 12), (13, 13), "
201 "(14, 14), (15, 15), (16, 16), (17, 17), (18, 18), (19, 19), (20, 20), (21, 21), (22, 22), (23, 23), (24, 24), "
202 "(25, 25), (26, 26), (27, 27), (28, 28), (29, 29), (30, 30), (31, 31), (32, 32), (33, 33), (34, 34), (35, 35), "
203 "(36, 36), (37, 37), (38, 38), (39, 39), (40, 40), (41, 41), (42, 42), (43, 43), (44, 44), (45, 45), (46, 46), "
204 "(47, 47), (48, 48), (49, 49), (50, 50), (51, 51), (52, 52), (53, 53), (54, 54), (55, 55), (56, 56), (57, 57), "
205 "(58, 58), (59, 59), (60, 60), (61, 61), (62, 62), (63, 63), (64, 64), (65, 65), (66, 66), (67, 67), (68, 68), "
206 "(69, 69), (70, 70), (71, 71), (72, 72), (73, 73), (74, 74), (75, 75), (76, 76), (77, 77), (78, 78), (79, 79), "
207 "(80, 80), (81, 81), (82, 82), (83, 83), (84, 84), (85, 85), (86, 86), (87, 87), (88, 88), (89, 89), (90, 90), "
208 "(91, 91), (92, 92), (93, 93), (94, 94), (95, 95), (96, 96), (97, 97), (98, 98), (99, 99), (5, 5), (NULL, "
209 "NULL), (NULL, NULL), (NULL, NULL), (NULL, NULL)"));
210
211 result = con.Query("SELECT * FROM integers WHERE i > 0 ORDER BY 1");
212 REQUIRE(CHECK_COLUMN(result, 0, {1, 2, 3, 4}));
213 REQUIRE(CHECK_COLUMN(result, 1, {1, 2, 3, 4}));
214
215 // attempt to append values that were inserted before (but failed)
216 // should work now
217 REQUIRE_NO_FAIL(con.Query("INSERT INTO integers VALUES (5, 5), (6, 6)"));
218
219 result = con.Query("SELECT * FROM integers WHERE i > 0 ORDER BY 1");
220 REQUIRE(CHECK_COLUMN(result, 0, {1, 2, 3, 4, 5, 6}));
221 REQUIRE(CHECK_COLUMN(result, 1, {1, 2, 3, 4, 5, 6}));
222
223 // now attempt conflicting updates
224 // conflict on PRIMARY KEY
225 REQUIRE_FAIL(con.Query("UPDATE integers SET i=4, j=100 WHERE i=1"));
226 // conflict on UNIQUE INDEX
227 REQUIRE_FAIL(con.Query("UPDATE integers SET i=100, j=4 WHERE j=1"));
228 // we can insert the old tuple normally
229 REQUIRE_NO_FAIL(con.Query("INSERT INTO integers VALUES (100, 100)"));
230
231 result = con.Query("SELECT * FROM integers WHERE i > 0 ORDER BY 1");
232 REQUIRE(CHECK_COLUMN(result, 0, {1, 2, 3, 4, 5, 6, 100}));
233 REQUIRE(CHECK_COLUMN(result, 1, {1, 2, 3, 4, 5, 6, 100}));
234}
235