1#include "catch.hpp"
2#include "test_helpers.hpp"
3
4using namespace duckdb;
5using namespace std;
6
7TEST_CASE("Test constraints with updates", "[constraints]") {
8 unique_ptr<QueryResult> result;
9 DuckDB db(nullptr);
10 Connection con(db);
11
12 // CHECK constraint
13 REQUIRE_NO_FAIL(con.Query("CREATE TABLE integers(i INTEGER, j INTEGER CHECK(i + j < 5), k INTEGER)"));
14 REQUIRE_NO_FAIL(con.Query("INSERT INTO integers VALUES (1, 2, 4)"));
15
16 // updating values that are not referenced in the CHECK should just work
17 REQUIRE_NO_FAIL(con.Query("UPDATE integers SET k=7"));
18 // update to a passing value should work
19 REQUIRE_NO_FAIL(con.Query("UPDATE integers SET i=i, j=3"));
20 REQUIRE_NO_FAIL(con.Query("UPDATE integers SET i=i, j=3"));
21 // now update the value so it doesn't pass
22 REQUIRE_FAIL(con.Query("UPDATE integers SET i=i, i=10"));
23 REQUIRE_FAIL(con.Query("UPDATE integers SET i=i, j=10"));
24 // now update the value without explicitly mentioning the other column
25 REQUIRE_NO_FAIL(con.Query("UPDATE integers SET j=2"));
26 REQUIRE_FAIL(con.Query("UPDATE integers SET j=10"));
27 // verify that the final result is correct
28 result = con.Query("SELECT * FROM integers");
29 REQUIRE(CHECK_COLUMN(result, 0, {1}));
30 REQUIRE(CHECK_COLUMN(result, 1, {2}));
31 REQUIRE(CHECK_COLUMN(result, 2, {7}));
32
33 REQUIRE_NO_FAIL(con.Query("DROP TABLE integers"));
34
35 // NOT NULL constraint
36 REQUIRE_NO_FAIL(con.Query("CREATE TABLE integers(i INTEGER NOT NULL, j INTEGER NOT NULL)"));
37 // insert a value that passes
38 REQUIRE_NO_FAIL(con.Query("INSERT INTO integers VALUES (1, 2)"));
39 // update to a passing value should work
40 REQUIRE_NO_FAIL(con.Query("UPDATE integers SET j=3"));
41 // now update the value so it doesn't pass
42 REQUIRE_FAIL(con.Query("UPDATE integers SET i=NULL"));
43 REQUIRE_FAIL(con.Query("UPDATE integers SET j=NULL"));
44 // verify that the final result is correct
45 result = con.Query("SELECT * FROM integers");
46 REQUIRE(CHECK_COLUMN(result, 0, {1}));
47 REQUIRE(CHECK_COLUMN(result, 1, {3}));
48}
49