0

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.

0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.