How can I create my numbers matching table populate from zero instead of one ?

Question

I'm using a numbers matching table, from one upwards. However, I realize I need to start at zero instead. Can't figure it out..

CREATE TABLE IF NOT EXISTS util_nums (n integer primary key 
    autoincrement not null);

insert into util_nums(n) select null from (select 0 as n union select 1 
union select 2 union select 3 union select 4 union select 5 union select 6 
union select 7 union select 8 union select 9 union select 10) a 
    cross join 
(select 0 as n union select 1 union select 2 union select 3 union select 4 
union select 5 union select 6 union select 7 union select 8 union select 9 
union select 10) b 
    cross join (select 0 as n union select 1 union select 2 
union select 3 union select 4 union select 5 union select 6 union select 7 
union select 8 union select 9 union select 10) c;

Answer 1

you can temporarly disable auto increment using

 SET sql_mode = 'NO_AUTO_VALUE_ON_ZERO';

But I suggest update value to 0 after insert

Answer 2

"SQLite Autoincrement"

The important part seems to be...

If no negative ROWID values are inserted explicitly, then automatically
generated ROWID values will always be greater than zero.

So? Create the table, insert a dummy record, with a forced id of -1, and then insert your data. Deleting the dummy record afterwards as/if necessary.

(Inserting a value with -1 will force the next inserted rwo to have an id of 0, assuming the table was otherwise empty.)

Answer 3

in sql server it would be easy if you create your table like this

CREATE TABLE util_nums (n as int primary key 
    identity(0,1) not null,anotherfieldtoholdthenulls integer);

the identity(0,1) means start from zero and increment by 1 ..

update

try using UPDATE SQLITE_SEQUENCE SET seq = -1 WHERE name = 'util_nums' before starting the insert, and see if it is allowed....

You should also be able to do INSERT INTO util_nums VALUES(0)

Answer 4

Hello,

If you're useing SQLite you should probably read this http://www.sqlite.org/autoinc.html . This caught my eye:

If the table has never before contained any data, then a ROWID of 1 is used.

It doesn't seem to be any way to force autoincrement to start from something other than 1. Also note that it may generate gaps by skipping numbers.

---

This may work but I have no means of testing at the moment:
Add a row with an ID of -1. Then delete it. It isn't clear from the documentation what happens when you have only negative IDs in the table.

Answer 5

Sqlite allows you to insert explicit values for the primary key fields:

insert into util_nums(n) values (0);

to Get a lot more rows insert quickly try this after that..

insert into util_nums default values;
insert into util_nums(n) select null from util_nums a, util_nums b, util_nums c, util_nums d;
insert into util_nums(n) select null from util_nums a, util_nums b, util_nums c, util_nums d;