1#include "catch.hpp"
2#include "test_helpers.hpp"
3
4using namespace duckdb;
5using namespace std;
6
7TEST_CASE("CHECK 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 CHECK(i < 5))"));
13 // insert value that passes
14 REQUIRE_NO_FAIL(con.Query("INSERT INTO integers VALUES (3)"));
15 // insert value that doesn't pass
16 REQUIRE_FAIL(con.Query("INSERT INTO integers VALUES (7)"));
17 // insert NULL
18 REQUIRE_NO_FAIL(con.Query("INSERT INTO integers VALUES (NULL)"));
19
20 result = con.Query("SELECT * FROM integers");
21 REQUIRE(CHECK_COLUMN(result, 0, {3, Value()}));
22
23 REQUIRE_NO_FAIL(con.Query("DROP TABLE integers;"));
24
25 // constraint on multiple columns
26 REQUIRE_NO_FAIL(con.Query("CREATE TABLE integers(i INTEGER CHECK(i + j < 10), j INTEGER)"));
27 REQUIRE_NO_FAIL(con.Query("INSERT INTO integers VALUES (3, 3)"));
28 // some values pass some values don't
29 REQUIRE_FAIL(con.Query("INSERT INTO integers VALUES (5, 5)"));
30 REQUIRE_FAIL(con.Query("INSERT INTO integers VALUES (3, 3), (5, 5)"));
31
32 result = con.Query("SELECT * FROM integers");
33 REQUIRE(CHECK_COLUMN(result, 0, {3}));
34 REQUIRE(CHECK_COLUMN(result, 1, {3}));
35
36 // subquery not allowed in CHECK
37 REQUIRE_FAIL(con.Query("CREATE TABLE integers2(i INTEGER CHECK(i > (SELECT 42)), j INTEGER)"));
38 // aggregate not allowed in CHECK
39 REQUIRE_FAIL(con.Query("CREATE TABLE integers2(i INTEGER CHECK(i > SUM(j)), j INTEGER)"));
40}
41