At the point when the check constraint is evaluated during the ALTER TABLE, the information that the column is NULLable and that the row's column value is NULL has been removed, but the column's value has not yet been updated with the auto-increment value, so the check constraint appears to be violated.

ALTER TABLE is unnecessary to reproduce the problem regarding check constraints on an auto-increment column:

MariaDB [test]> create or replace table t2( a int auto_increment primary key, b int );

Query OK, 0 rows affected (0.10 sec)

MariaDB [test]> insert into t2( b ) values( NULL );

Query OK, 1 row affected (4.40 sec)

MariaDB [test]> select * from t2;

+---+------+

| a | b    |

+---+------+

| 1 | NULL |

+---+------+

1 row in set (0.00 sec)

Without the check constraint, the auto-increment column gets a value of 1. Here is what happens with the check constraint:

MariaDB [test]> create or replace table t2( a int auto_increment primary key, b int, check( a > 0 or a is null ) );

Query OK, 0 rows affected (0.09 sec)

MariaDB [test]> insert into t2( b ) values( NULL );

ERROR 4025 (23000): CONSTRAINT `CONSTRAINT_1` failed for `test`.`t2`

MariaDB [test]>

This occurs because auto-increment columns are given their incremented value as part of the storage engine process of writing a row, whereas check constraints are evaluated on the row prior to writing the row, at a time when the auto-increment column has been cleared and therefore has a value of zero.

Let's disallow auto-increment columns in CHECK constraints. They are similarly disallowed in virtual columns and should be disallowed in DEFAULT expressions too (please check that).

We'd need to refactor auto-increment implementation to make all this working as expected. It cannot be done in 10.2.

I have fixed the bug by disallowing auto-increment columns in CHECK constraints, DEFAULT expressions and VIRTUAL COLUMN expressions in commit https://github.com/MariaDB/server/commit/b7aa15458db95149af4f473a90009844a16e9db8.

Hi Sergei,

I've fixed the bug by disallowing auto-increment columns in CHECK constraints, DEFAULT value expressions and VIRTUAL COLUMN expressions, in commit https://github.com/MariaDB/server/commit/b7aa15458db95149af4f473a90009844a16e9db8. Please review.

Thanks,
Jacob

I have merged the final version of the fix, committed in https://github.com/MariaDB/server/commit/38af34bb2143f2a7ce82d2e241d8995f419a7f29, into the 10.2 branch.

I have merged the final version of the fix, committed in https://github.com/MariaDB/server/commit/38af34bb2143f2a7ce82d2e241d8995f419a7f29, into the 10.2 branch.

By making this fix, you've denied using columns in kind of 'recursive' approach.
I have this table which defines nodes and hierarchy between them

create table `track_tree` (

    `node_id` bigint not null auto_increment primary key,

    `parent_node_id` bigint,

    constraint `track_tree_node_id_parent_node_id_ck` check (parent_node_id <> node_id)

)

So check constraint here is not allowed anymore.
Can you suggest any workaround besides having separate table for "relations" between nodes?

because of the way auto-increment columns are implemented, neither check constraints, nor virtual columns, not default expressions work with them, unfortunately.

as a workaround you might try to use sequences, perhaps they'll help.