MySQL PHP insert new row where rowNum column values always remain chronological by date and time

I am trying to create a SQL query (or multiple queries and logic in PHP) to insert new rows into MySQL db table (say table name is student) where the rowNum column values always remain chronological dependent on a separate date/time column. This means that if the row I’m inserting has the greatest/max/latest date/time it should be inserted normally and the rowNum value will just increment by one. I already have that implemented. The issue is that when the row I’m inserting has a date/time value that falls between two existing rows. Then the rowNum value for the row I’m inserting needs to be set to be the same as the rowNum value for the second of the two rows that I’m trying to insert “between” (Note: I understand the table is unordered list/set; however, the rowNum needs to adjust as in it is being insert “between”). Then, the rest of the rows after (chronologically speaking) this newly inserted row need to have their rowNum value incremented by one. Please see below example for further clarification:

id is autoincrement id column and primary key. rowNum is a “reference” id that is not necessarily unique, but does increment for each new insertion. If you go through each rowNum value, they should always be in chronological order based on date/time column. dateTime is not a default datetime timestamp, instead it is a varchar field in the format shown below.

Table: student

id	rowNum	dateTime
1	4	09-17-2021 14:00
2	5	09-17-2021 16:32
3	6	09-18-2021 19:11
4	7	09-22-2021 13:01

Then, when insert a new row with dateTime: 09-17-2021 15:21 the table should be as follows:

Table: student

id	rowNum	dateTime
1	4	09-17-2021 14:00
5	5	09-17-2021 15:21
2	5	09-17-2021 16:32
3	6	09-18-2021 19:11
4	7	09-22-2021 13:01

Then, the rowNum values after the inserted row should increment as follows:

Table: student

id	rowNum	dateTime
1	4	09-17-2021 14:00
5	5	09-17-2021 15:21
2	6	09-17-2021 16:32
3	7	09-18-2021 19:11
4	8	09-22-2021 13:01

Lastly, I understand this is a strange thing to do. I’ve made this concern clear and suggested this should be done a different way; but, due to unique constraints I have to do it this way where the rowNum values are chronological.

Answer

INSERT cannot modify other rows than the row(s) it inserts. You also cannot use a trigger, because a trigger cannot update the same table (it could cause infinite loops).

So you must do an UPDATE statement after you finish your INSERT. Best if you do this in a transaction.

But actually it’s easier to do the UPDATE first:

START TRANSACTION;

UPDATE student SET rowNum=rowNum+1 WHERE rowNum >= 5;

INSERT INTO student SET rowNum=5, dateTime='09-17-2021 15:21';

COMMIT;

It’s better to do the UPDATE first because if you INSERT the new row first, then it’ll get included in the condition of the UPDATE.

This still has a problem, if more than one client is inserting a row concurrently. It could cause lock conflicts or deadlocks. You might need to use LOCK TABLES first to prevent this. But that will block your opportunity to do concurrent inserts. In other words, every transaction that does an INSERT will need to wait for others to commit.

You should also consider:

• What happens to the rowNum values if a row is deleted?

• What happens to the rowNum values if a dateTime is updated?

• What prevents a client from updating a rowNum value directly, and throwing off the unbroken sequence?