github.com/cockroachdb/cockroach@v20.2.0-alpha.1+incompatible/pkg/sql/logictest/testdata/logic_test/check_constraints (about) 1 # LogicTest: !3node-tenant 2 #### column CHECK constraints 3 4 statement ok 5 CREATE TABLE t1 (a INT CHECK (a > 0), to_delete INT, b INT CHECK (b < 0) CHECK (b > -100)) 6 7 statement error could not parse "3.3" as type int 8 INSERT INTO t1 VALUES ('3.3', 0, -1) 9 10 statement ok 11 INSERT INTO t1 VALUES ('3', 0, -1) 12 13 statement ok 14 INSERT INTO t1 VALUES (3, 0, -1) 15 16 statement ok 17 ALTER TABLE t1 DROP COLUMN to_delete 18 19 statement ok 20 INSERT INTO t1 (a, b) VALUES (4, -2) 21 22 statement error pgcode 23514 pq: failed to satisfy CHECK constraint \(a > 0:::INT8\) 23 INSERT INTO t1 VALUES (-3, -1) 24 25 statement error pgcode 23514 pq: failed to satisfy CHECK constraint \(b < 0:::INT8\) 26 INSERT INTO t1 VALUES (3, 1) 27 28 statement error pgcode 23514 pq: failed to satisfy CHECK constraint \(b > \(-100\):::INT8\) 29 INSERT INTO t1 VALUES (3, -101) 30 31 statement ok 32 INSERT INTO t1 (b, a) VALUES (-2, 4) 33 34 statement ok 35 INSERT INTO t1 (a) VALUES (10) 36 37 statement ok 38 INSERT INTO t1 (b) VALUES (-1) 39 40 statement ok 41 CREATE TABLE t2 (a INT DEFAULT -1 CHECK (a >= 0), b INT CHECK (b <= 0), CHECK (b < a)) 42 43 statement error pgcode 23514 pq: failed to satisfy CHECK constraint \(a >= 0:::INT8\) 44 INSERT INTO t2 (b) VALUES (-2) 45 46 ### Rename column with check constraint 47 48 statement ok 49 ALTER TABLE t2 RENAME COLUMN b TO c 50 51 statement error pgcode 23514 pq: failed to satisfy CHECK constraint \(c <= 0:::INT8\) 52 INSERT INTO t2 (a, c) VALUES (2, 1) 53 54 statement error pgcode 23514 pq: failed to satisfy CHECK constraint \(c < a\) 55 INSERT INTO t2 (a, c) VALUES (0, 0) 56 57 statement ok 58 INSERT INTO t2 (a, c) VALUES (2, -1) 59 60 statement ok 61 CREATE TABLE t3 (a INT, b INT CHECK (b < a)) 62 63 statement ok 64 INSERT INTO t3 (a, b) VALUES (3, 2) 65 66 statement error pgcode 23514 pq: failed to satisfy CHECK constraint \(b < a\) 67 INSERT INTO t3 (a, b) VALUES (2, 3) 68 69 # Verify we don't accept count(*) 70 statement error variable sub-expressions are not allowed in CHECK 71 CREATE TABLE t4 (a INT, b INT CHECK (count(*) = 1)) 72 73 # no subqueries either. 74 statement error variable sub-expressions are not allowed in CHECK 75 CREATE TABLE t4 (a INT, b INT CHECK (EXISTS (SELECT * FROM t2))) 76 77 # non-boolean expressions are errors 78 statement error pq: expected CHECK expression to have type bool, but '1' has type int 79 CREATE TABLE t4 (a INT CHECK(1)) 80 81 statement error pq: expected CHECK expression to have type bool, but 'a' has type int 82 CREATE TABLE t4 (a INT CHECK(a)) 83 84 # Function calls in CHECK are okay. 85 statement ok 86 CREATE TABLE calls_func (a INT CHECK(abs(a) < 2)) 87 88 statement ok 89 INSERT INTO calls_func VALUES (1), (-1) 90 91 statement error failed to satisfy CHECK 92 INSERT INTO calls_func VALUES (-5) 93 94 # Aggregate function calls in CHECK are not ok. 95 statement error aggregate functions are not allowed in CHECK 96 CREATE TABLE bad (a INT CHECK(sum(a) > 1)) 97 98 # Window function calls in CHECK are not ok. 99 statement error window functions are not allowed in CHECK 100 CREATE TABLE bad (a INT CHECK(sum(a) OVER () > 1)) 101 102 # fail on bad check types 103 statement error pq: unsupported binary operator: <bool> - <bool> 104 CREATE TABLE t4 (a INT CHECK (false - true)) 105 106 statement error pgcode 42703 column "b" does not exist, referenced in "a < b" 107 CREATE TABLE t4 (a INT, CHECK (a < b), CHECK (a+b+c+d < 20)) 108 109 statement ok 110 CREATE TABLE t4 (a INT, b INT DEFAULT 5, c INT, d INT, CHECK (a < b), CONSTRAINT "all" CHECK (a+b+c+d < 20)) 111 112 statement ok 113 INSERT INTO t4 (a, b) VALUES (2, 3) 114 115 statement error pgcode 23514 failed to satisfy CHECK constraint 116 INSERT INTO t4 (a) VALUES (6) 117 118 statement ok 119 INSERT INTO t4 VALUES (1, 2, 3, 4) 120 121 statement ok 122 INSERT INTO t4 VALUES (NULL, 2, 22, NULL) 123 124 statement error pgcode 23514 failed to satisfy CHECK constraint 125 INSERT INTO t4 VALUES (1, 2, 3, 19) 126 127 query II 128 SELECT * from t3 129 ---- 130 3 2 131 132 statement error pgcode 23514 failed to satisfy CHECK constraint 133 UPDATE t3 SET b = 3 WHERE a = 3 134 135 statement ok 136 UPDATE t3 SET b = 1 WHERE a = 3 137 138 statement error pgcode 23514 failed to satisfy CHECK constraint 139 UPDATE t4 SET a = 2 WHERE c = 3 140 141 statement ok 142 UPDATE t4 SET a = 0 WHERE c = 3 143 144 statement ok 145 CREATE TABLE t5 (k INT PRIMARY KEY, a INT, b int CHECK (a > b)) 146 147 statement error pgcode 23514 failed to satisfy CHECK constraint 148 INSERT INTO t5 VALUES (1, 10, 20) ON CONFLICT (k) DO NOTHING 149 150 statement ok 151 INSERT INTO t5 VALUES (1, 10, 9) ON CONFLICT (k) DO NOTHING 152 153 # We only check constraints if an insert or update actually occurs. 154 statement ok 155 INSERT INTO t5 VALUES (1, 10, 20) ON CONFLICT (k) DO NOTHING 156 157 # n.b. the fully-qualified name below is required, as there are two providers of 158 # the column named `k` here, the original table and the `excluded` pseudo-table. 159 statement error pgcode 23514 failed to satisfy CHECK constraint 160 INSERT INTO t5 VALUES (2, 11, 12) ON CONFLICT (k) DO UPDATE SET b = 12 WHERE t5.k = 2 161 162 statement error pgcode 23514 failed to satisfy CHECK constraint 163 UPSERT INTO t5 VALUES (2, 11, 12) 164 165 statement ok 166 UPSERT INTO t5 VALUES (2, 11, 10) 167 168 query III rowsort 169 SELECT * FROM t5 170 ---- 171 1 10 9 172 2 11 10 173 174 statement ok 175 UPSERT INTO t5 VALUES (2, 11, 9) 176 177 query III rowsort 178 SELECT * FROM t5 179 ---- 180 1 10 9 181 2 11 9 182 183 statement error pgcode 23514 failed to satisfy CHECK constraint 184 INSERT INTO t5 VALUES (2, 11, 12) ON CONFLICT (k) DO UPDATE SET b = 12 WHERE t5.k = 2 185 186 statement error pgcode 23514 failed to satisfy CHECK constraint 187 UPSERT INTO t5 VALUES (2, 11, 12) 188 189 statement error pgcode 23514 failed to satisfy CHECK constraint 190 INSERT INTO t5 VALUES (2, 11, 12) ON CONFLICT (k) DO UPDATE SET b = t5.a + 1 WHERE t5.k = 2 191 192 query III rowsort 193 SELECT * FROM t5 194 ---- 195 1 10 9 196 2 11 9 197 198 statement error variable sub-expressions are not allowed in CHECK 199 CREATE TABLE t6 (x INT CHECK (x = (SELECT 1))) 200 201 # Check auto-generated constraint names. 202 203 statement ok 204 CREATE TABLE t7 ( 205 x INT, 206 y INT, 207 z INT, 208 CHECK (x > 0), 209 CHECK (x + y > 0), 210 CHECK (y + z > 0), 211 CHECK (y + z = 0), 212 CONSTRAINT named_constraint CHECK (z = 1), 213 FAMILY "primary" (x, y, z, rowid) 214 ) 215 216 query TT 217 SHOW CREATE TABLE t7 218 ---- 219 t7 CREATE TABLE t7 ( 220 x INT8 NULL, 221 y INT8 NULL, 222 z INT8 NULL, 223 FAMILY "primary" (x, y, z, rowid), 224 CONSTRAINT check_x CHECK (x > 0:::INT8), 225 CONSTRAINT check_x_y CHECK ((x + y) > 0:::INT8), 226 CONSTRAINT check_y_z CHECK ((y + z) > 0:::INT8), 227 CONSTRAINT check_y_z1 CHECK ((y + z) = 0:::INT8), 228 CONSTRAINT named_constraint CHECK (z = 1:::INT8) 229 ) 230 231 # Check that table references are dequalified in their stored representation. 232 233 statement error no data source matches prefix: different_table 234 CREATE TABLE t8 ( 235 a INT, 236 CHECK (different_table.a > 0) 237 ) 238 239 statement error no data source matches prefix: different_database.t8 240 CREATE TABLE t8 ( 241 a INT, 242 CHECK (different_database.t8.a > 0) 243 ) 244 245 statement ok 246 CREATE TABLE t8 ( 247 a INT, 248 CHECK (a > 0), 249 CHECK (t8.a > 0), 250 CHECK (test.t8.a > 0) 251 ) 252 253 query TT 254 SHOW CREATE TABLE t8 255 ---- 256 t8 CREATE TABLE t8 ( 257 a INT8 NULL, 258 FAMILY "primary" (a, rowid), 259 CONSTRAINT check_a CHECK (a > 0:::INT8), 260 CONSTRAINT check_a1 CHECK (a > 0:::INT8), 261 CONSTRAINT check_a2 CHECK (a > 0:::INT8) 262 ) 263 264 statement ok 265 CREATE DATABASE test2 266 267 statement ok 268 CREATE TABLE test2.t ( 269 a INT, 270 CHECK (a > 0), 271 CHECK (t.a > 0), 272 CHECK (test2.t.a > 0) 273 ) 274 275 # Use multiple column families. 276 277 statement ok 278 CREATE TABLE t9 ( 279 a INT PRIMARY KEY, 280 b INT, 281 c INT, 282 d INT, 283 e INT, 284 FAMILY (a), 285 FAMILY (b), 286 FAMILY (c), 287 FAMILY (d, e), 288 CHECK (a > b), 289 CHECK (d IS NULL) 290 ) 291 292 statement ok 293 INSERT INTO t9 VALUES (5, 3) 294 295 statement error pgcode 23514 failed to satisfy CHECK constraint \(a > b\) 296 INSERT INTO t9 VALUES (6, 7) 297 298 statement ok 299 UPDATE t9 SET b = 4 WHERE a = 5 300 301 statement error pgcode 23514 failed to satisfy CHECK constraint \(a > b\) 302 UPDATE t9 SET b = 6 WHERE a = 5 303 304 statement ok 305 UPDATE t9 SET a = 7 WHERE a = 4 306 307 statement error pgcode 23514 failed to satisfy CHECK constraint \(a > b\) 308 UPDATE t9 SET a = 2 WHERE a = 5 309 310 # Regression test for #36293. Make sure we don't panic with a false check 311 # constraint. 312 statement ok 313 CREATE TABLE t36293 (x bool) 314 315 statement ok 316 ALTER TABLE t36293 317 ADD COLUMN y INT 318 CHECK ( 319 CASE 320 WHEN false 321 THEN x 322 ELSE false 323 END 324 ) 325 326 # Regression tests for #46675. 327 statement ok 328 CREATE TABLE t46675isnull (k int, a int, CHECK ((k, a) IS NULL)) 329 330 # IS NULL is true when the operand is a tuple with all NULL values. 331 statement ok 332 INSERT INTO t46675isnull VALUES (NULL, NULL) 333 334 statement ok 335 CREATE TABLE t46675isnotnull (k int, a int, CHECK ((k, a) IS NOT NULL)) 336 337 # IS NOT NULL is false when the operand is a tuple with at least one NULL 338 # value. 339 statement error pgcode 23514 pq: failed to satisfy CHECK constraint \(\(k, a\) IS NOT NULL\) 340 INSERT INTO t46675isnotnull VALUES (1, NULL)