MySQL Auto Increment a column data based on another column with foreign key

[shades]

Hi Guys,

 

I have a table like:

 

ID      FID         COLOR

1       volvo      red

2       volvo      blue

3       volvo      green

4       bmw       white

5       bmw       black

 

Now i need one more column with stores kind of sequence like below

 

ID      FID         COLOR   SID

1       volvo      red              1

2       volvo      blue            2

3       volvo      green         3

4       bmw       white          1

5       bmw       black          2

 

 

I did read about Triggers, Count and use of variables with row numbers. But I am not sure which is the correct way. I use InnoDB and I want the column to work properly even in case of deletes and updates. How can i achieve this through a PHP Insert and select query ? 

 

Thanks in advance:)

 

[Jacques1]

This is an SQL question, so it belongs into the SQL forum. Thread moved.

 

What's the point of those extra IDs? Unless you absolutely must have them to make your boss, customers or users happy, forget about it. Maintaining a perfect number sequence per group is an absolute nightmare which requires you to completely lock the table for every operation and constantly move numbers around. Trust me, you don't want this.

[gizmola]

Yes it is very complicated.

 

You can roll your own sequences. It is going to greatly reduce concurrency whenever you have to allocate a key, but essentially you create a table with your keys, and an integer/id column.

 

 

In your example:

Sequences

-----------

fid (pk)

color (pk)

sequence int -- contains the last sequence

 

This is essentially your own implementation of auto_increment.

 

Whenever you have to insert a new row in your table, you do a lookup by fid/color, increment the row and return the value, which can then use in your insert of your main table.

 

This has lots of issues with it, including the fact you can't do bulk insert operations.

 

It will not handle deletions, just as auto_increment doesn't handle deletions, and I don't know how you would expect them to.

 

If I delete sequence #1, what is supposed to happen? If you want to define some rules/procedural code for that, you could probably do something there but the idea of renumbering a bunch of different rows in the group is really unusual.

 

I do have a hard time seeing the use of this column, especially considering that it reflects and maintains the same ordering that would happen naturally with the auto_increment key, or a creation only timestamp column were it to be added. In other words, what is important about having 1-2-3 in a group vs. the raw id, which already would sort exactly the same way?