Mysql – thesql how to copy foreign key data from one table to another

foreign keyMySQL

I have 3 tables.

Each table has its own primary key "id"

table 2 has a foreign key that references table 1's primary key

table 3 has a foreign key that references table 2's primary key.

I want to add to t.3 the foreign key that links t.2 – t.1 so that t.3 is also linked to t.1. I cant figure out how to copy the relevant column from t.2 to t.3.

Best Answer

That depends on youir table definition .

If your id fileds are auto increment

CREATE TABLE table1(id int AUTO_INCREMENT PRIMARY KEY)

CREATE TABLE table2(id int AUTO_INCREMENT PRIMARY KEY,t1id int,
    FOREIGN KEY (t1id) REFERENCES table1(id))

CREATE TABLE table3(id int AUTO_INCREMENT PRIMARY KEY,t2id int,
    FOREIGN KEY (t2id) REFERENCES table2(id))

INSERT INTO table1 VALUES(NULL);

INSERT INTO table2 VALUEs(NULL,LAST_INSERT_ID());

INSERT INTO table3 VALUEs(NULL,LAST_INSERT_ID());

SELECT t1.*,t2.*,t3.* 
FROM table1 t1 INNER JOIN table2 t2 ON t1.id = t2.t1id
INNER JOIN table3 t3 ON t2.id = t3.t2id

id | id | t1id | id | t2id
-: | -: | ---: | -: | ---:
 1 |  1 |    1 |  1 |    1

db<>fiddle here

In other cases you have to get the last id number from the father table, which is dependent on which type if id you have.

Related Question