MySQL auto generate following number

Question

There are 4 columns in my relationship table:

1. ID
2. CID - course id
3. LID - related lesson unique id
4. course_based_id - explanation below

There are bunch of lessons and courses with unique id's in another tables called lessons and courses. This table is, asociation table between them. Basically it assigns unique lesson id to unique course id. But.. There is need for course based id of lesson. I mean, for example lets take a look at first and second rows from screenshot

Lid 1 is course 2's first lesson, lid 3 is course 2's second lesson... And so on. So if I insert cid 2, lid 128 it must third lesson of course 2.

Now, the problem is, I want to auto generate this number while insert:

$q="INSERT INTO `courses-lessons` (`cid`, `lid`) VALUES (?,?)";

but have no idea how to do it. Some MySQL function? or something inside query?

Answer 1

If you're using MyISAM engine to store this table, this can be done relatively easy: you just define the fields this way:

cid INT NOT NULL,
course_based_id INT NOT NULL AUTO_INCREMENT,
...
PRIMARY KEY (cid, course_based_id)

Then any INSERT will automatically assign a new (+1) value for course_based_id based on cid. Here's a Fiddle illustrating this concept.

You can find more info about it in MySQL documentation.

Unfortunately, there's no such mechanism in InnoDB engine. So perhaps you can get away with the following routine (it's a pseudocode, there are some nuances):

1) lock table for writing; 
2) @x = SELECT MAX(course_based_id) WHERE cid = %cid_to_be_inserted%;
3) INSERT (..., course_based_id) VALUES (..., @x + 1);
4) unlock table;

I'd suggest checking this answer; it's for PostgreSQL, not MySQL, but describes one possible implementation in some details.

UPDATE: Here's an example of actual set of queries implementing that behaviour:

CREATE TABLE tx
  (id INT NOT NULL, cid INT NOT NULL, lid INT NOT NULL,
   course_based_id INT NOT NULL,
   PRIMARY KEY (id),
   UNIQUE KEY (cid, course_based_id)
);

LOCK TABLE tx WRITE, tx AS t READ;

INSERT INTO tx (id, cid, lid, course_based_id)
  SELECT 2, 2, 1, 
         1 + COALESCE(
               (SELECT MAX(course_based_id) 
                  FROM tx AS t 
                 WHERE cid = 2), 
             0);

 INSERT INTO tx (id, cid, lid, course_based_id)
   SELECT 3, 2, 3, 
          1 + COALESCE(
                (SELECT MAX(course_based_id) 
                  FROM tx AS t WHERE cid = 2),
              0);

 UNLOCK TABLES;

As you see, there's no direct calculation of the next course_based_id value, yet we got the sequence here.

Answer 2

If you want referential integrity (Foreign Keys) you are using InnoDB, so the (nice) MyISAM trick does not work.

What you can do is generate those course_based_id during the inserts into the table.

Write a - not so complex - procedure that does the inserts and allow no other code to insert into this table - excpet through this procedure. You'll also need a procedure for the updates (and one for the deletes, if you mind having gaps in this course_based_id).

So, instead of the:

INSERT INTO courses_lessons 
  (cid, lid) 
VALUES 
  ( ?cid, ?lid ) ;

you'll have, enclosed in a stored procedure to catch any errors (duplicates, etc):

INSERT INTO courses_lessons 
  (cid, lid, course_based_id) 
SELECT 
  ?cid, ?lid, 
  1 + COALESCE( ( SELECT MAX(course_based_id) 
                  FROM courses_lessons 
                  WHERE cid = ?cid
                ), 0)
FROM
  dual ;

---

For this table, you don't really need the id column. A primary key on (cid, lid) will be enough for identifying rows. You'll also need to add a unique key on (cid, course_based_id):

ALTER TABLE courses_lessons 
  ADD CONSTRAINT UQ_cid_course_based_id
    UNIQUE cid_course_based_id_UQ
      (cid, course_based_id) ;