Question 375638 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – Auto Increment value for non-primary key column

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

Tags:

4

Answers


  1. 0

    You could calculate the field issue_number, there is no need to store it:

    SELECT 
   id, 
   project_id, 
   row_number() over (partition by project_id order by id) as issue_number,
   content 
FROM Issue;

    or you could store it when you need the actual value of issue_number often….

    Login or Signup to reply.
  2. 0

    You could use a subquery for this. In it you get the maximum issue number for a project and then you insert the issue row with a number that’s one higher. Something like this:

    INSERT INTO Issue (project_id, issue_number, content) 
VALUES (3, 
        (SELECT MAX(I.issue_number) + 1 FROM Issue AS I WHERE project_id = 3), 
        "This is  a test.");

    There’s no change that the issue number will go stale because you perform the subquery and insert in one SQL statement.

    To make this query performant I recommend to create an index on the combination of the project_id and issue_number columns:

    CREATE INDEX IssueInsert on Issue  (project_id, issue_number);

    Note that we have to use the AS I alias to prevent MySQL from saying: "ERROR: Table ‘Issue ‘ is specified twice, both as a target for ‘INSERT’ and as a separate source for data.".

    Login or Signup to reply.
  3. 0

    To ensure that the issue_number column increments correctly and avoids concurrent insert problems, you can use a combination of triggers and sequences in MariaDB. Here’s an approach to achieve this:

    1. Create a Sequence:
      First, create a sequence to generate unique numbers for the issue_number column. Sequences are designed to handle concurrent access and guarantee unique values.

      CREATE SEQUENCE issue_number_sequence START WITH 1;

    2. Use a Trigger:
      Next, create a trigger that automatically sets the issue_number when inserting a new row into the Issue table. This trigger will use the sequence to generate the next value for issue_number based on the project_id.

      DELIMITER //

      CREATE TRIGGER before_issue_insert
      BEFORE INSERT ON Issue
      FOR EACH ROW
      BEGIN
      SET NEW.issue_number = NEXT VALUE FOR issue_number_sequence;
      END;
      //

      DELIMITER ;

    With this setup, whenever a new row is inserted into the Issue table, the trigger before_issue_insert will automatically set the issue_number to the next value from the issue_number_sequence based on the current project_id. This approach ensures that issue_number increments correctly and avoids concurrent insert problems.

    Note: Make sure to adjust the sequence and trigger names, as well as any other specifics, based on your database schema and requirements.

    Login or Signup to reply.
  4. 0

    One way is to create another table, IssueSequence, that serves as a SEQUENCE table capable of holding the last Issue key created on a project basis.

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

create table if not exists IssueSequence (
    project_id integer PRIMARY KEY,
    max_issue_number integer,
    FOREIGN KEY(project_id) REFERENCES Project(id)
         ON DELETE CASCADE
         ON UPDATE CASCADE
);

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)
        ON DELETE CASCADE
        ON UPDATE CASCADE
);

START TRANSACTION;
INSERT INTO Project(name) VALUES('MyProject');
SET @last_project_id = last_insert_id();
INSERT INTO IssueSequence VALUES(@last_project_id, 0);
COMMIT;

START TRANSACTION;
UPDATE IssueSequence SET max_issue_number = last_insert_id(max_issue_number + 1)
    WHERE project_id = @last_project_id;
SET @last_issue_id = last_insert_id();
INSERT INTO Issue(project_id, issue_number, content) VALUES(@last_project_id, @last_issue_id, 'Test Issue 1');
COMMIT;

START TRANSACTION;
UPDATE IssueSequence SET max_issue_number = last_insert_id(max_issue_number + 1)
    WHERE project_id = @last_project_id;
SET @last_issue_id = last_insert_id();
INSERT INTO Issue(project_id, issue_number, content) VALUES(@last_project_id, @last_issue_id, 'Test Issue 2');
COMMIT;

SELECT * FROM Project;
SELECT * FROM Issue;
    id name
    1 MyProject
    id project_id issue_number content
    1 1 1 Test Issue 1
    2 1 2 Test Issue 2
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search