Creating a "secondary ID" in mysql.

Question

I have a table that stores "records" and already has primary key.

Table A
=======
id (INT) PK auto_increments
project_id (INT)
record_text (TEXT)

This table stores "records" for all of my projects. It is desirable to have a secondary key that auto increments for the projects.

Example: If project 1 had id's of (1,5,8,9). It would be desirable to store them and present them to the user as (1,2,3,4).

I do not have the ability to use TRANSACTIONS and I'm having trouble thinking of a way of doing this without causing concurrency issues.

Hopefully I got my problem across clearly. Thank you in advance for the help.

Edit (Example): Assuming the structure:
id, project_id, record text
1 1 testing
2 1 testing
3 2 testing
4 1 testing

My ID's for project 1 would 1,2,4. But I'm looking for a way to store and display them for project 1 as 1,2,3

Answer 1

You can create your project_id field to auto_increment as well without making it a key.

Answer 2

I can't tell from your question, but if you just want to present a cleanly numbered list to the user; I would handle this in the UI; and not even worry about storing it in the database.

If you really want to store it in the DB, I'd look into using a trigger that fires on INSERTED, and sets your value there.

Answer 3

You can generate row numbers using MySQL variables:

select
    id
,   project_id
,   if (@last_id = project_id, @n, @n := @n + 1) as RunningProjId
,   record_text
,   @last_id := project_id
from (select @n := 0, @last_id := -1) r, A
order by project_id

The row with the if increases the @n variable if the project differs from the last row. This relies on the order by clause to function. The row with `@last_id := project_id saves the project_id for reference when the next row is selected.

The first part of the from clause is the variable initialization. The second part is your table called A.

Answer 4

I'm not sure what I was thinking, too late in the day probably, but I was able to achieve this by adding a column to the table:

id, pid, project_id, record_text

pid being the numbering system for the project.

The INSERT:

INSERT INTO A (id, pid, project_id, record_text) 
VALUES (
    NULL, 
    (SELECT COALESCE(new_id, 0) + 1 FROM (SELECT MAX(new_id) AS new_id FROM atest WHERE project_id = 1) AS x),
    1,
    'some text'
);

Now as my project changes my pid, id based off the project, auto-increments too:

id    pid    project_id    record_text
1     1      1             testing 123
2     2      1             testing 123
3     1      2             testing 123
4     3      1             testing 123

Thank you all for your input and I apologize for my poor description of the problem.