How do i create a foreign key on table creation

Question

I am trying to create a automatic table creation with a foreign key connected so that i can show the data in the same table. How should i make the connection between the tables as they are created.

Note: The tables are created at the same time. with the Palle_tbs first in the load order.

I dont know if Alter Table can be used in this example, since the tables are created at the same time. if so how should i do it?

i've tried to make a reference to the other table like so.

addcsv.php This is where i call the methods. $lastest_filename is not getting used atm.

$csv->createPalleTable($latest_filename);
$csv->insertPalleTable($latest_filename, array_map("toNumber", fgetcsv($openfile, 1000, ";")));

$csv->createHoejde1($latest_filename);
$csv->insertHoejde1Table($latest_filename, array_map("toNumber", fgetcsv($openfile, 1000, ";")));

I can add more code, just think it would make it more confusing.

Csv.php This is where i make the tables and the inserts.

EDIT i have updated my attempt

I expect the tables to be connected. the results i've gotten was FK_palle = 0

public function createHoejde1($latest_filename){
    return $this->db->toList("CREATE TABLE IF NOT EXISTS `csvHoejde1`( 
        id INT(11) AUTO_INCREMENT PRIMARY KEY,
        Name VARCHAR(50), 
        Datum VARCHAR(50), 
        Property VARCHAR(50), 
        Criterion VARCHAR(50),
        Type VARCHAR(50), 
        Nominal DECIMAL(10,2),    
        Actual DECIMAL(10,2),     
        Tolminus DECIMAL(10,2),   
        Tolplus DECIMAL(10,2),    
        Dev DECIMAL(10,2),
        FK_palle INT(11) REFERENCES Palle_tbs(`id`))
      ");}

public function insertHoejde1Table($latest_filename ,$getdata){
    return $this->db->toList("INSERT INTO `csvHoejde1` (`Name`, `Datum`, `Property`, `Criterion`, `Type`, `Nominal`, `Actual`,`Tolminus`,`Tolplus`,`Dev`)
    VALUES (:Name, :Datum, :Property, :Criterion, :Type, :Nominal, :Actual, :Tolminus, :Tolplus, :Dev)",
        [
        ':Name' => $getdata[0],
        ':Datum'=> $getdata[1],
        ':Property'=> $getdata[2],
        ':Criterion'=> $getdata[3],
        ':Type'=> $getdata[4],
        ':Nominal'=> $getdata[5], 
        ':Actual'=> $getdata[6],  
        ':Tolminus'=> $getdata[7],
        ':Tolplus'=> $getdata[8], 
        ':Dev'=> $getdata[9]      
        ]
    );}

public function createPalleTable($latest_filename){
    return $this->db->toList("CREATE TABLE IF NOT EXISTS `Palle_tbs`(
        id INT(11) AUTO_INCREMENT PRIMARY KEY,
        Palle_nr VARCHAR(50), 
        Varenummer VARCHAR(50), 
        Ordre_nummer VARCHAR(50), 
        Operatoer VARCHAR(50), 
        Maskine_nr VARCHAR(50), 
        Raavare_batch_nr VARCHAR(50), 
        Dato_ur_stillet VARCHAR(50), 
        Klokke_fuldsvejst VARCHAR(50), 
        Fuldstoebning_af_klokke VARCHAR(50),
        Afgratning_af_overflade VARCHAR(50), 
        Vaegt DECIMAL(10,2))
      ");}

public function insertPalleTable($latest_filename ,$palledata){
    return $this->db->toList("INSERT INTO `Palle_tbs` (`Palle_nr`, `Varenummer`, `Ordre_nummer`, `Operatoer`, `Maskine_nr`,
     `Raavare_batch_nr`, `Dato_ur_stillet`,`Klokke_fuldsvejst`,`Fuldstoebning_af_klokke`,`Afgratning_af_overflade`,`Vaegt`)
    VALUES (:Palle_nr, :Varenummer, :Ordre_nummer, :Operatoer, :Maskine_nr, :Raavare_batch_nr, :Dato_ur_stillet,
     :Klokke_fuldsvejst, :Fuldstoebning_af_klokke, :Afgratning_af_overflade, :Vaegt)",
        [

        ':Palle_nr' => $palledata[0],
        ':Varenummer'=> $palledata[1],
        ':Ordre_nummer'=> $palledata[2],
        ':Operatoer'=> $palledata[3],
        ':Maskine_nr'=> $palledata[4],
        ':Raavare_batch_nr'=> $palledata[5],
        ':Dato_ur_stillet'=> $palledata[6],
        ':Klokke_fuldsvejst'=> $palledata[7],
        ':Fuldstoebning_af_klokke'=> $palledata[8],
        ':Afgratning_af_overflade'=> $palledata[9],
        ':Vaegt'=> $palledata[10]
        ]
    );}

result is on FK_palle is 0

Answer 1

If you want to reference table1 into table2, you need first to create table1

In your statement, you wrote FOREIGN KEY (FK_product) REFERENCES product_tb(product_id). There is no product_id field in product_tb table, you meant to reference the primary key id

Then, you can use the FOREIGN KEY constraint.

In example :

CREATE TABLE table1
( --         ^----^-----------------------------------+
    id INT(6) NOT NULL PRIMARY KEY AUTO_INCREMENT, -- |
--  ^^---------------------------------------------------+
    someData VARCHAR(255)                          -- |  |
);                                                 -- |  |
                                                   -- |  |
CREATE TABLE table2                                -- |  |
(                                                  -- |  |
    id INT(6) NOT NULL PRIMARY KEY AUTO_INCREMENT, -- |  |
    otherData VARCHAR(255), --                        |  |
    table1_id INT(6), --               +--------------+  |
 -- ^-------^-------+                  |      +----------+
    --              |                  |      |
    --          v-------v            v----v  vv
    FOREIGN KEY table1_id REFERENCES table1 (id)
);

Then, you can use INSERT queries to fill the tables. For table2, you'll have to insert the foreign key, it won't be done automatically (your DB doesn't know which row is referenced)

In example :

INSERT INTO table1
--         id
--         |    someData
--         |       |
--      v-----v  v---v
VALUES (DEFAULT, "foo"),
       (DEFAULT, "bar");

INSERT INTO table2
--         id
--         |    otherData
--         |        |          table1_id
--         |        |              |
--      v-----v  v------v          v
VALUES (DEFAULT, "blabla",         1),
       (DEFAULT, "bob the sponge", 1),
       (DEFAULT, "John Smith",     1),
       (DEFAULT, "forty two",      2),
       (DEFAULT, "Hello world",    2);