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

Answer 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