auto_increment, multiple columns

[robingo]

A) What I want:

 

I have a table with a field id.

This is my primary key, and it's set as auto_increment.

When someone adds a record, it will generate a unique id, automaticly.

Example: these id's are in the table:

1

2

3

4

 

When I delete the record where id=4, the next record added will get id=5 (and not 4!).

This is how I want it do be.

 

 

B) What I get:

 

I want to do the same thing, but with two columns, id1 and id2.

I've set id1 and id2 as primary key, and id2 set as auto_increment.

This is what happens:

Table:

 

id1 | id2

1  | 1

1  | 2

1  | 3

1  | 4

2  | 1

2  | 2

 

When I delete, for example, the record with id1=1 and id2=4,

the next added record will get:

1  | 4,

but I want it to get:

 

1  | 5.

 

This is basicly what happens in "A)". It "remembers" where it stopped counting,

and it continues, even if a row was deleted!

 

Can anyone help me with this?

I googled a lot but couldn't find a solution...

Thanks a lot!

[Lyleyboy]

Only way really is to set one to auto increment and insert it.

Now get the max of the id to find the row you just inserted and update it.

 

It's worked for me in the past.