How to avoid using max(id) when creating a self-referencing row

Question

I have a MySQL table

id | ref_id

where ref_id should be equal to one of the available id values (including the one of the same row). id is also auto-incremented.

In a stored procedure, I want to add a row to this table where ref_id = id. I can do it by

DECLARE prev_id INT;
SELECT MAX(id) INTO prev_id FROM `table`;
INSERT INTO table (ref_id) VALUES (prev_id + 1)

However it is not recommended to use MAX(id) for this purpose. I should use LAST_INSERT_ID() instead. But how can I do it if I haven't inserted anything else?

Answer 1

Schema

-- drop table selfie;
create table selfie
(   id int auto_increment primary key,
    thing varchar(40) not null,
    ref_id int not null,
    descr varchar(40) not null
);

Stored Procedure

drop procedure if exists spInsertOneSelfRef;

DELIMITER $$
create procedure spInsertOneSelfRef
(   pThing varchar(40),
    pRef int, -- 0 if to self, >0 if not 
    pDescr varchar(40)
)
begin
  insert selfie(thing,ref_id,descr) values (pThing,pRef,pDescr);
    -- Moment A
  IF (pRef=0) then
    -- self-reference request, ditch the zero that was just inserted
    set @theId:= last_insert_id();
    update selfie set ref_id=@theId where id=@theId;
    -- MOMENT B
  END IF;
end
$$
DELIMITER ;

Test it

-- truncate table selfie;
call spInsertOneSelfRef('frog',0,'a selfie'); -- 
call spInsertOneSelfRef('cat',1,''); -- 
call spInsertOneSelfRef('mouse',2,''); 
call spInsertOneSelfRef('dog',3,''); -- 
call spInsertOneSelfRef('abcd',0,'a selfie'); -- 

View results

select * from  selfie;
+----+-------+--------+----------+
| id | thing | ref_id | descr    |
+----+-------+--------+----------+
|  1 | frog  |      1 | a selfie |
|  2 | cat   |      1 |          |
|  3 | mouse |      2 |          |
|  4 | dog   |      3 |          |
|  5 | abcd  |      5 | a selfie |
+----+-------+--------+----------+

It is possible to insert a null into ref_id with a few tweaks. And to deal with concurrency control in the split second between Moment A and Moment B listed in the stored proc. I leave those to the reader, unless you plead me to do it.

---

Option B (ref_id schema change)

drop table selfie;
create table selfie
(   id int auto_increment primary key,
    thing varchar(40) not null,
    ref_id int, -- NULL means I have no parent
    descr varchar(40) not null,
    constraint fk_blahblah foreign key (ref_id) references selfie(id)
);

Test

call spInsertOneSelfRef('abcd',null,'no parent'); -- good
call spInsertOneSelfRef('pqrs',1,''); --  good
call spInsertOneSelfRef('zzz',44,''); -- bad (as expected, FK failure)

View

+----+-------+--------+-----------+
| id | thing | ref_id | descr     |
+----+-------+--------+-----------+
|  1 | abcd  |   NULL | no parent |
|  2 | pqrs  |      1 |           |
+----+-------+--------+-----------+

oh I have a parent now, no problem, update the ref_id column