SQLite: double primary key and autoincrement

Question

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

`AUTOINCREMENT` is a keyword you can only use in a column constraint `PRIMARY KEY` clause. I don't have any idea for the actual problem though. What's the issue behind your problem - maybe the problem can be reworked to an easier one. — laalto, Apr 24 '13 at 20:26

score 1 · Accepted Answer · edited May 23 '17 at 11:43

1

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

edited May 23 '17 at 11:43

Community

1
1

answered Apr 24 '13 at 21:12

DuKes0mE

1,101
19
30

score 0 · Answer 2 · answered Apr 24 '13 at 21:31

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);
}

SQLite: double primary key and autoincrement

2 Answers2