Will AutoIncrement work with Check constraints?

Question

The question is simple: In SQLite, if I choose to AutoIncrement a primary key of type NUMERIC which has a check constraint like CHECK(LENGTH(ID) == 10), will it work correctly inserting the first value as 0000000001 and so on?

Answer 1

No, that does not work. Adding a check does not magically also add a way of fullfilling the check to insert the data.

See this SQLFiddle.

Answer 2

If you want to restrict the value of an autoincrement column like that, you need to seed the internal sequence table. (There are other ways.)

create table foo (
  foo_id integer primary key autoincrement,
  other_columns char(1) default 'x',
  check (length(foo_id) = 10 )
);

insert into sqlite_sequence values ('foo', 999999999);

Application code is allowed to modify the sqlite_sequence table, to add new rows, to delete rows, or to modify existing rows.

Source

insert into foo (other_columns) values ('a');
select * from foo;

1000000000|a

Trying to insert 11 digits makes the CHECK constraint fail.

insert into foo values (12345678901, 'a');

Error: CHECK constraint failed: foo

One alternative is to insert a "fake" row with the first valid id number immediately after creating the table. Then delete it.

create table foo(...);

insert into foo values (1000000000, 'a');
delete from foo;

Now you can insert normally.

insert into foo (other_columns) values ('b');
select * from foo;

1000000001|b

Answer 3

In fact the ID's length is 1, so it doesn't work.