Android Database SQLLite AutoIncrement not incrementing and primary constraint violated

im creating a Table with this command :

create table if not exists Event ( _id long INTEGER AUTO INCREMENT,repeatEvery long ,repeating int ,startTime long ,title text ,primary key (_id) );

also tried id without the _ prefix, and then i fetch all rows with

database.query("Event",null , null, null, null, null, null);

and then i go over the results printing the ID column :

if (c.moveToFirst()) {
            do {
                System.out.println(c.getLong(c.getColumnIndexOrThrow("_id")));  

            } while (c.moveToNext());
        }

all of the results are zeros! idea any one?

Solution

SQLITE by default adds an INTEGER AUTOINCREMENT for you. Search for ROWID. The creation of a column that replicates this functionality will just be created as an alias to ROWID.

I did some more digging around and found out what some of the issues are. First of all, SQLITE is garbage.

Here is the correct SQL statement you need to use.

CREATE TABLE Event (_id INTEGER PRIMARY KEY AUTOINCREMENT,repeatEvery long ,repeating int ,startTime long ,title text)

I issued the one in your code sample, and it had the type of the _id column as "INTEGER AUTO INCREMENT". The SQLITE parser is very generous and accepted that as a datatype. It had no idea what to do with it, and just treated it as a blob, and none of the constraints worked. So, even though your DDL was incorrect, SQLITE was nice enough to take it's best guess at what was wrong and didn't bother to let you know. :[

If you print the schema of your apps' Event table, you'll should see the INTEGER AUTO INCREMENT issue.

Another issue to watch out for,

If you're writing your insert statements on your own, you need to write in one of these two ways.

insert into Event (repeatEvery, repeating, startTime, title) values (1,2,3, "title");

insert into Event values (NULL, 1,2,3, "title");

the SQLITE Helper class should handle this for you.