如何使用外键填充子表 - MySql

Question

I am working on MySQL database. I am new to it that is why I am facing a problem. The problem is populating the child table with foreign key which is referencing to the parent table. I have two tables employee which contains following columns

• id as a primary key,
• first_name
• last_name
• birth_date

and a borrowed table which contains following columns

• ref as a primary key
• employId as a foreign key
• book

The employeeId is referencing the primary key id of the employee table. So simply it means the one employee with same id can borrow multiple books. When I insert some data into the employee table It get inserted, but when I have to insert data into the borrowed table, I have to manually insert the value in employeeId column. Isn't it supposed to be populated automatically. or I am misunderstanding the concept of the foreign key.

My SQL Code

  $uname = "root";
  $pass = "";
  $sname ="localhost";
  $db ="nady";
   //Making database connection
  $con = mysqli_connect($sname,$uname,$pass,$db);

 $t1 = "CREATE TABLE IF NOT EXISTS employee (
    id smallint(5) unsigned AUTO_INCREMENT NOT NULL,
    firstname varchar(30),
    lastname varchar(30),
    birthdate date,
    PRIMARY KEY (id)
) ENGINE=InnoDB";
$con->query($t1);



$t2 = "CREATE TABLE IF NOT EXISTS borrowed (
    ref int(10) unsigned NOT NULL auto_increment,
    employeeid smallint(5) unsigned NOT NULL,
    book varchar(50),
    PRIMARY KEY (ref),
    FOREIGN KEY (employeeid) REFERENCES employee(id) ON UPDATE CASCADE ON DELETE CASCADE
) ENGINE=InnoDB";
$con->query($t2);
if(!$con->query($t2)){
    echo $con->error;
}


$i1 = "INSERT INTO employee VALUES(NULL,\"Nadeem\",\"Ahmad\",22)";
$con->query($i1);

$i2 = "INSERT INTO borrowed VALUES(NULL,1,\"Ahmad\")";
$con->query($i2);
if(!$con->query($i2)){
    echo $con->error;
}

Simple what I need is ; For example an employee with id 1. Who borrowed 3 books. So in the borrowed table the employeeId column will have three rows with values 1 and different books name. My point is how would I populate the employeeId column when I am inserting the data into it. Let say, John have have borrowed three books and have id 1 then how would I insert data to borrowed table with employeeId of john. I need the query for it. and also query to retrieve the books borrowed by john.

</div>

Answer 1

The foreign key is used to link two tables, indicating that the field in a column (employId from borrowed, in your case) refers to the PRIMARY KEY of another table (id from employee).

When you're inserting a new line in borrowed, you have to indicate the user that is taking that book, to insert it in that line. You have to know the user that is doing it. If you have foreign key, you need the id of that user, which is supposed to be his unique identifier. To insert that John has taken a book, you need to know that John's id is 1.

If the user is already in your employee table and you know his first and last name, you can get the id with a simple select...

SELECT id FROM employee WHERE first_name='John' AND last_name='Smith'

... and then you can do the insert with the id obtained.

If it's new user, you need to add the user first to employee, then get the new id and then insert the new line in borrowed, to do this without having to re-query to employee table to get the new id, you can use the PHP mysqli::$insert_id/mysqli_insert_id function, that gives you the PRIMARY key of the last query. For example...

$con->query("INSERT INTO employee (first_name,last_name) VALUES ('Mark','Whatever')");
$newemployeeid = $con->insert_id;
$con->query("INSERT INTO borrowed (employeeid,book) VALUES (".$newemployeeid.",'Awesome Book Title')");

I hope it helps