MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE t1 (t1id INT PRIMARY KEY)
SELECT 1 t1id UNION SELECT 2;
CREATE TABLE t2 (t2id INT PRIMARY KEY, t1id INT,
                 FOREIGN KEY (t1id) REFERENCES t1 (t1id))
SELECT 11 t2id, 1 t1id UNION SELECT 22, 2;
SELECT * FROM t1; SELECT * FROM t2;

Records: 2  Duplicates: 0  Warnings: 0

Foreign key creation is not allowed with CREATE TABLE as SELECT and CREATE TABLE with START TRANSACTION statement.

UPDATE t2 SET t1id = 3 WHERE t2id = 22;

Table 'fiddle.t2' doesn't exist

CREATE TRIGGER tr_bu_t2
BEFORE UPDATE ON t2
FOR EACH ROW 
SET NEW.t1id = @t1id;

Table 'fiddle.t2' doesn't exist

SET @t1id = 1;
UPDATE t2 SET t1id = 3 WHERE t2id = 22;

Table 'fiddle.t2' doesn't exist

SELECT * FROM t1; SELECT * FROM t2;

t1id

1

2

Table 'fiddle.t2' doesn't exist