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>