Cannot add or update a child row: a foreign key constraint fails, but row exists

Have an odd one here, getting the usual

MySQL Error 1452 - Cannot add or update a child row: a foreign key constraint fails

Except the foreign key in question exists, and I know so because the following (pseudo code) precedes the failing query.

A bit of context, this is being run inside a procedure, which has a couple of steps, which the first is to get an existing (foreign) record ID or create one. After some debugging it is obtaining the one from the joined table.

Here's an example of the code (not actual due to IP purposes)

CREATE PROCEDURE doStuff(v_namey VARCHAR, v_ref INT)
BEGIN

  SELECT id FROM tableA WHERE ref = v_ref INTO @tableA_id;
  IF (@tableA_id IS NULL) THEN
    INSERT INTO tableA (.....);
    SET @tableA_id = LAST_INSERT_ID();
  END IF;

  -- Fails here due to the FK constraint failing on `tablea_id`.
  INSERT INTO tableB (`name`, tablea_id, ...)
  VALUES (v_namey, @tableA_id, ...);

END IF;

This fails on the tablea_id value failing the FK constant on (only) that field. The obvious idea is that my @tableA_id is not a valid value, except it's what the SELECT ... INTO gave me.

This is on the latest (Azure) MySQL database, and I've triple checked all the tables and constraints, data types, etc.

What I've also tried so far...

1. Cast the variable to UNSIGNED INT (just in case some thing quirky)
2. Run a COMMIT; before the failing insert.
3. Captured the exception (via DECLARE EXIT HANLDER syntax), to provide the data that it's pulling.

What is looks like is the SELECT .. INTO is getting a valid ID from the table, and that value stays stored in the variable, but the INSERT is just not validating the id.

Is this a bug, or is there some transactional stuff that I'm missing?

Appreciate any ideas to try.

You have 2 issues in your example.

• as pointed by @P.Salmon, you should set null to user-defined-variable @tableA_id. User defined variables are session specific. (https://dev.mysql.com/doc/refman/8.4/en/user-variables.html)
• LAST_INSERT_ID() takes value for auto_generated values. If you put tableA id column in insert statement, this value not affect on last_insert_id. (https://dev.mysql.com/doc/refman/8.4/en/information-functions.html#function_last-insert-id)

Shortly

• After statement insert into tableA(tableA_id,a_name) values(1000,'name-100') LAST_INSERD_ID() returns 0 or value from previous insert statement with auto generated value.
• After statement insert into tableA(a_name) values('name-auto') LAST_INSERD_ID() returns value of tableA_id for last insert statement.

Simple test

create table tableA (tableA_id int auto_increment primary key, a_name varchar(100));

insert into tableA values (100,'NameA-100');
select LAST_INSERT_ID() as last_id;

insert into tableA (a_name) values ('NameA-auto');
select LAST_INSERT_ID() as last_id;

insert into tableA values (1000,'NameA-1000');
select LAST_INSERT_ID() as last_id;

select * from tableA;

fiddle

See example with your procedure

create table tableA (tableA_id int auto_increment primary key, a_name varchar(100));
create table tableB (id int primary key auto_increment
  ,b_name varchar(100)
  ,refA int 
  ,foreign key (refA) references tableA(tableA_id));

create table logs (id int primary key auto_increment ,descr varchar(200));

Try insert rows to tableA and table_B

insert into tableA values (1,'NameA-1');
insert into tableB values (100,'NameB-1',1);

In test procedure we have 2 cases for test.

• with auto_increment
• with manual set of id

CREATE PROCEDURE doStuff(v_namey VARCHAR(100), v_ref INT)
BEGIN
  -- this row not exist in your procedure
  set @tableA_id=null;

  SELECT tableA_id FROM tableA WHERE tableA_id = v_ref INTO @tableA_id;
  IF (@tableA_id IS NULL) THEN
    if v_namey='Test1' then
      INSERT INTO tableA values (1000,'NameA-1000');
    else
      INSERT INTO tableA (a_name) values ('NameA-auto');
    end if;
    SET @tableA_id = LAST_INSERT_ID();
  END IF;

  insert into logs(descr) values(concat(v_namey,',',v_ref,',',@tableA_id));

  -- Fails here due to the FK constraint failing on `tablea_id`.
  INSERT INTO tableB (b_name, refA)
  values (v_namey, @tablea_id);

END;

Procedure logs to table `logs`` values ... @tableA_id.

Test call with auto_increment

call doStuff('testX',2);

Second - no autoincriment. LAST_INSERT_ID() returns value from previous call. You get 101 instead 1000.

call doStuff('test1',4);

Cannot add or update a child row: a foreign key constraint fails (`fiddle`.`tableB`, CONSTRAINT `tableB_ibfk_1` FOREIGN KEY (`refA`) REFERENCES `tableA` (`tableA_id`))

fiddle

If we run test in another order, we can see in log table

• LAST_INSERT_ID() returns 0, when we manually set id value.

call doStuff('test1',2);

Cannot add or update a child row: a foreign key constraint fails (`fiddle`.`tableB`, CONSTRAINT `tableB_ibfk_1` FOREIGN KEY (`refA`) REFERENCES `tableA` (`tableA_id`))

call doStuff('testX',4);

fiddle