CODEWITHSUNDEEP
sqliteandroid-sqlite
Jason Posit
Jason Posit

Reputation: 1323

SQLite: double primary key and autoincrement

I am trying to define a two-row primary key such that the second column is autoincremented for each individual value of the first row.

For instance we would have:

X Y
A 1
A 2
A 3
A 4
A 5
B 1
B 2
B 3
C 1
D 1
E 1
E 2

I would like to do an:

create table t (x text, y integer autoincrement) primary key (x, y);

but I get an error from SQLite saying I have an error near "autoincrement". What can I do? (even an integer valued x would do as long as I can get it to work).

Then I would like to get the above values with:

insert into t (x) values ('A');
insert into t (x) values ('A');
insert into t (x) values ('A');
insert into t (x) values ('A');
insert into t (x) values ('A');
insert into t (x) values ('B');
insert into t (x) values ('B');
insert into t (x) values ('B');
insert into t (x) values ('C');
insert into t (x) values ('D');
insert into t (x) values ('E');
insert into t (x) values ('E');

THanks,

Jason Posit

Upvotes: 3

Views: 2249

Answers (2)

Tianyun Ling
Tianyun Ling

Reputation: 1097

You can't do this from sqlite. What you need is to do this from an upper level, which is your program. The pseudo code would be like:

char current_symbol='A';
int count=0;
for(loop all your data)
{
    if(current_symbol == same text)
    {
      count++;    
    }
    else
    {
      current_symbol = same text;
      count=1;
    }

    insert into t (x,y) values ('A', count);
}

Upvotes: 0

DuKes0mE
DuKes0mE

Reputation: 1131

You can't autoincrement if you have the same number twice or more often in your table. I know what you want to do but it cant be done with autoincrement.

But you could something like
create table t (x text not null, y integer not null) primary key (x, y);

Also see this: sqlite: multi-column primary key with an auto increment column

Upvotes: 1

Related Questions