Description:
Foreign key constraint fails when it shouldn't, but only when statements are executed from within a function and there is existing data in table `a`, and not in table b.
In example, the following similar bits of code work (presuming the tables and functions from the 'How to repeat' section have been loaded')
select ins();
This works by it self. Its only when the prior INSERT INTO a (`a_id`) VALUES (1); has been executed does it fail.
INSERT INTO a (`a_id`) VALUES (1);
INSERT INTO a () VALUES ();
SELECT last_insert_id() INTO @new_a_id;
INSERT INTO b () VALUES ();
SELECT last_insert_id() INTO @new_b_id;
INSERT INTO a_to_b (a_id , b_id )
VALUES (@new_a_id , @new_b_id);
This also works. (which is essentially just expanding the execution of function `ins`). Note that the initial insert into `a` that breaks the call to `ins` is still there.
Additionally, data in table `b` doesn't seem to cause a problem, since
INSERT INTO b (`b_id`) VALUES (1);
select ins();
works. Also, it seems as if there is no problem if there is data in both tables a and be, since
INSERT INTO a (`a_id`) VALUES (1);
INSERT INTO b (`b_id`) VALUES (1);
select ins();
works.
How to repeat:
CREATE TABLE a (
`a_id` INT NOT NULL AUTO_INCREMENT,
PRIMARY KEY(a_id)
) ENGINE=InnoDB;
CREATE TABLE b (
`b_id` INT NOT NULL AUTO_INCREMENT,
PRIMARY KEY (b_id)
) ENGINE=InnoDB;
CREATE TABLE a_to_b (
`a_id` INT NOT NULL,
`b_id` INT NOT NULL,
INDEX (a_id),
INDEX (b_id),
FOREIGN KEY (a_id) REFERENCES a(a_id),
FOREIGN KEY (b_id) REFERENCES b(b_id)
) ENGINE=InnoDB;
DELIMITER |
CREATE FUNCTION ins()
RETURNS INT
LANGUAGE SQL
BEGIN
DECLARE new_a_id INT ;
DECLARE new_b_id INT ;
INSERT INTO a () VALUES ();
SELECT last_insert_id() INTO new_a_id;
INSERT INTO b () VALUES ();
SELECT last_insert_id() INTO new_b_id;
INSERT INTO a_to_b (a_id , b_id )
VALUES (new_a_id , new_b_id);
RETURN new_a_id;
END |
DELIMITER ;
/* Time to have an error */
INSERT INTO a (`a_id`) VALUES (1);
select ins();
Description: Foreign key constraint fails when it shouldn't, but only when statements are executed from within a function and there is existing data in table `a`, and not in table b. In example, the following similar bits of code work (presuming the tables and functions from the 'How to repeat' section have been loaded') select ins(); This works by it self. Its only when the prior INSERT INTO a (`a_id`) VALUES (1); has been executed does it fail. INSERT INTO a (`a_id`) VALUES (1); INSERT INTO a () VALUES (); SELECT last_insert_id() INTO @new_a_id; INSERT INTO b () VALUES (); SELECT last_insert_id() INTO @new_b_id; INSERT INTO a_to_b (a_id , b_id ) VALUES (@new_a_id , @new_b_id); This also works. (which is essentially just expanding the execution of function `ins`). Note that the initial insert into `a` that breaks the call to `ins` is still there. Additionally, data in table `b` doesn't seem to cause a problem, since INSERT INTO b (`b_id`) VALUES (1); select ins(); works. Also, it seems as if there is no problem if there is data in both tables a and be, since INSERT INTO a (`a_id`) VALUES (1); INSERT INTO b (`b_id`) VALUES (1); select ins(); works. How to repeat: CREATE TABLE a ( `a_id` INT NOT NULL AUTO_INCREMENT, PRIMARY KEY(a_id) ) ENGINE=InnoDB; CREATE TABLE b ( `b_id` INT NOT NULL AUTO_INCREMENT, PRIMARY KEY (b_id) ) ENGINE=InnoDB; CREATE TABLE a_to_b ( `a_id` INT NOT NULL, `b_id` INT NOT NULL, INDEX (a_id), INDEX (b_id), FOREIGN KEY (a_id) REFERENCES a(a_id), FOREIGN KEY (b_id) REFERENCES b(b_id) ) ENGINE=InnoDB; DELIMITER | CREATE FUNCTION ins() RETURNS INT LANGUAGE SQL BEGIN DECLARE new_a_id INT ; DECLARE new_b_id INT ; INSERT INTO a () VALUES (); SELECT last_insert_id() INTO new_a_id; INSERT INTO b () VALUES (); SELECT last_insert_id() INTO new_b_id; INSERT INTO a_to_b (a_id , b_id ) VALUES (new_a_id , new_b_id); RETURN new_a_id; END | DELIMITER ; /* Time to have an error */ INSERT INTO a (`a_id`) VALUES (1); select ins();