Make one ID with auto_increment depending on another ID - possible?

Question

I want to make a small ticket-system for a project server which has several projects. So far the TicketID will be counted globally, i.e. there is a project A and a ticket with TicketID 1 and another project B and the ticket for this project will get TicketID 2 - like this:

(TicketID, ProjectID)
(1, 1)
(2, 1)
(3, 1)
(4, 2)
(5, 2)
(6, 3)

But I think it would be better to count the TicketID depending on the ProjectID such as:

(TicketID, ProjectID)
(1, 1)
(2, 1)
(3, 1)
(1, 2)
(2, 2)
(1, 3)

Here the table:

CREATE  TABLE IF NOT EXISTS tickets (
    TicketID  INT UNSIGNED NOT NULL AUTO_INCREMENT,
    ProjectID  INT UNSIGNED NOT NULL,
    ...
    PRIMARY KEY (TicketID, ProjectID) ,
    FOREIGN KEY (ProjectID) REFERENCES projects (ProjectId),    
    ...
);

Is it possible to make the TicketID with auto_increment depending on the ProjectID with SQL? Or is there no way with SQL and I have to set the IDs with my PHP-code manually?

Answer 1

Why not have a "next ticket id" field on the project table - when creating a new ticket, get this value, increment it and set the ticket id to the previous value? All in a transaction, obviously.

Answer 2

Honestly, I usually try to persuade requestors against this. Having a unique Ticket numbering may be better, you only need to remember/print/display one piece of information. As long as you provide lists and counts of the tickets, I think that it is a much clearer structure. Of course, your business logic may require separate numbering. If a straight line without interruption is required, I usually use triggers. M.

Answer 3

MySQL supports this:

create table history (
    id integer not null,
    version integer auto_increment not null,
    content text not null,
    primary key(id,version)
);

As far as I know there is not direct support of such feature in Sqlite or PostgreSQL so I use just:

insert into history(id,version,content)
select 10,(SELECT COALESCE(MAX(version),0)+1 FROM history WHERE id=10),'Hello';

For them, but remember this is not real autoincrement and may create not unique keys if you remove something from the table.