Behavior of NOT NULL DEFAULT

Help
#1

Hi,

I ran into an issue inserting data into a table where a given tinyint value was NULL, and there is a NOT NULL constraint on the corresponding column in SingleStore.

However, the column does have a default value; it’s defined like:

 `foo` tinyint(1) NOT NULL DEFAULT 0

When inserting, the following error is returned:
NULL supplied to NOT NULL column 'foo' at row 122058

Shouldn’t it default to 0 instead of erroring out?

I have also had this occur when the NOT NULL column is the shard key, even with a default value set. When / how is the DEFAULT value used?

(self-hosted, version 7.5.8)

#2

NOT NULL is an integrity constraint. Any insert/update that tries to make a NOT NULL column have a NULL value will error. E.g.

singlestore> create table t(a int not null default 0);
Query OK, 0 rows affected (1.07 sec)

singlestore> insert t values (1);
Query OK, 1 row affected (0.12 sec)

singlestore> insert t values(NULL);
ERROR 1048 (23000): Leaf Error (172.17.0.2:3308): Column 'a' cannot be null

The DEFAULT is only used whey you provide no value for the column, e.g.

singlestore> insert t values();
Query OK, 1 row affected (0.12 sec)

singlestore> select * from t;
+---+
| a |
+---+
| 0 |
| 1 |
+---+
1 Like
#3

Thanks, that clarifies it! Would be nice if it were to treat “NULL” the same as “nothing” but I see the distinction.

1 Like
#4

Glad Eric could give some clarification. Thank you for your time! :pray:

We will make a note of your feedback. Appreciate it, Erica! :raised_hands: