Auto Increment value for non-primary key column

ghz 8months ago ⋅ 138 views

In my MariaDB I have two tables project and issue, the issue table contains all issues created for all projects

create table if not exists Project(
  id integer PRIMARY KEY auto_increment,
  `name` varchar(200) NOT NULL
 );

 create table if not exists Issue(
  id integer PRIMARY KEY AUTO_INCREMENT,
  project_id integer,
   issue_number integer,
   content text,
    FOREIGN KEY (project_id)
        REFERENCES Project(id)
   );

The issue_number is per project and starts always from 1, how can increment it by 1 and resolve concurrent insert problems?

I can't use select max(issue_number) + 1 from Issue where project_id=X to determine the new value because could be stale

Answers

To increment the issue_number for each project while avoiding concurrency issues, you can use MariaDB's AUTO_INCREMENT feature in combination with a trigger. Here's how you can achieve this:

  1. Modify the Issue table: Change the issue_number column to an AUTO_INCREMENT column. Since AUTO_INCREMENT columns are unique to a table and are incremented automatically, this ensures that each project will have unique and incrementing issue_numbers.

    CREATE TABLE IF NOT EXISTS Issue(
    id INTEGER PRIMARY KEY AUTO_INCREMENT,
    project_id INTEGER,
    issue_number INTEGER AUTO_INCREMENT,
    content TEXT,
    FOREIGN KEY (project_id) REFERENCES Project(id)
);

  2. Create a Trigger: Create a trigger that sets the issue_number to NULL upon insertion. This allows the AUTO_INCREMENT mechanism to generate the next sequential number for each project.

    DELIMITER //
CREATE TRIGGER reset_issue_number
BEFORE INSERT ON Issue
FOR EACH ROW
BEGIN
    SET NEW.issue_number = NULL;
END;
//
DELIMITER ;

With this setup, each time you insert a new row into the Issue table, MariaDB will automatically increment the issue_number column for that project, starting from 1. This ensures that you won't have concurrency issues, as MariaDB handles the incrementation internally.

#mariadb