1#include "catch.hpp"
2#include "test_helpers.hpp"
3
4using namespace duckdb;
5using namespace std;
6
7TEST_CASE("NOT NULL 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 NOT NULL)"));
13 // insert normal value
14 REQUIRE_NO_FAIL(con.Query("INSERT INTO integers VALUES (3)"));
15 // insert NULL
16 REQUIRE_FAIL(con.Query("INSERT INTO integers VALUES (NULL)"));
17
18 // update normal value
19 REQUIRE_NO_FAIL(con.Query("UPDATE integers SET i=4"));
20 // update NULL
21 REQUIRE_FAIL(con.Query("UPDATE integers SET i=NULL"));
22
23 REQUIRE_NO_FAIL(con.Query("CREATE TABLE integers_with_null(i INTEGER)"));
24 REQUIRE_NO_FAIL(con.Query("INSERT INTO integers_with_null VALUES (3), (4), (5), (NULL);"));
25
26 //! INSERT from SELECT with NULL
27 REQUIRE_FAIL(con.Query("INSERT INTO integers (i) SELECT * FROM integers_with_null"));
28 //! INSERT from SELECT without NULL
29 REQUIRE_NO_FAIL(con.Query("INSERT INTO integers (i) SELECT * FROM "
30 "integers_with_null WHERE i IS NOT NULL"));
31
32 result = con.Query("SELECT * FROM integers ORDER BY i");
33 REQUIRE(CHECK_COLUMN(result, 0, {3, 4, 4, 5}));
34
35 REQUIRE_NO_FAIL(con.Query("UPDATE integers SET i=4 WHERE i>4"));
36
37 result = con.Query("SELECT * FROM integers ORDER BY i");
38 REQUIRE(CHECK_COLUMN(result, 0, {3, 4, 4, 4}));
39}
40