Persistent table is ALLOWED to declare a FOREIGN KEY that REFERENCES a Global Temporary Table

--source include/have_innodb.inc

CREATE GLOBAL TEMPORARY TABLE parent (id INT PRIMARY KEY) ENGINE=InnoDB ON COMMIT PRESERVE ROWS;

--echo # Expected ER_CANNOT_ADD_FOREIGN, but child table creation succeeds

CREATE TABLE child (id INT PRIMARY KEY, pid INT, FOREIGN KEY(pid) REFERENCES parent (id)) ENGINE=InnoDB;

INSERT INTO parent VALUES (1);

--echo # The INSERT failed even though the parent table has rows with id = 1

--error ER_NO_REFERENCED_ROW_2

INSERT INTO child VALUES (10, 1);

--echo # only NULL accepted:

INSERT INTO child VALUES (10, NULL);

SELECT * FROM child;

CREATE GLOBAL TEMPORARY TABLE parent (id INT PRIMARY KEY) ENGINE=InnoDB ON COMMIT PRESERVE ROWS;

# Expected ER_CANNOT_ADD_FOREIGN, but child table creation succeeds

CREATE TABLE child (id INT PRIMARY KEY, pid INT, FOREIGN KEY(pid) REFERENCES parent (id)) ENGINE=InnoDB;

INSERT INTO parent VALUES (1);

# The INSERT failed even though the parent table has rows with id = 1

INSERT INTO child VALUES (10, 1);

ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`child`, CONSTRAINT `1` FOREIGN KEY (`pid`) REFERENCES `parent` (`id`))

# only NULL accepted:

INSERT INTO child VALUES (10, NULL);

SELECT * FROM child;

id	pid

10	NULL