Why the FOREIGN KEY doesn't work for the LOOP?

Question

I've created two tables: 1.Table

CREATE TABLE IF NOT EXISTS  us_tAddress (
 id INT(6) UNSIGNED   AUTO_INCREMENT PRIMARY KEY,
 country VARCHAR(100),
 city VARCHAR(100),
 zip VARCHAR(100),
 street VARCHAR(100),
 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
 )

The "address_id" column in the "us_tUser" table is a FOREIGN KEY in the "us_tUser" table. 2.TABLE

CREATE TABLE IF NOT EXISTS us_tUser(
 id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 address_id INT (6) UNSIGNED,
 first_name VARCHAR(100),
 last_name VARCHAR(100),
 position VARCHAR(100),
 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 FOREIGN KEY (address_id)  REFERENCES us_tAddress(id)
   )

For inserting values into second table from array, I am using Loop:

foreach ($array as $row => $value) {


             $first_name    = mysqli_real_escape_string($conn,$value['first_name']);
             $last_name    = mysqli_real_escape_string($conn,$value['last_name']);
             $position     = mysqli_real_escape_string($conn,$value['position']);
             $sql = "INSERT INTO us_tUser ( first_name, last_name, position) 
             VALUES ( '".$first_name."', '".$last_name."', '".$position."' )";
             mysqli_query($conn,$sql);

What I should change in the script, because the result in address_id column is NULL. (Let's see picture bellow)

Thank you in Advance!

"because the result in address_id column is NULL" - And what should it be instead? — Paul Spiegel, May 26 '20 at 07:05
Foreign key sets NOTHING. It only CHECKS that the referenced value exists, and prevents insertion if not exists. *What I should change in the script, because the result in address_id column is NULL.* You must set it explicitly. — Akina, May 26 '20 at 07:08
Null is allowed in address_id and the FK will not see this as an error. You could change address_id to not null if that's what you want.. — P.Salmon, May 26 '20 at 07:20
@PaulSpiegel - I would like to get column id from first table into second table instead of NULL values in address_id column. — Random2020, May 26 '20 at 10:56

sachin kumara liyanage · Answer 1 · 2020-05-26T07:48:10.703

you did not stop add null value to address_id column so you can add NOT NULL to us_tUser table and create it

CREATE TABLE IF NOT EXISTS us_tUser(
 id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 address_id INT (6) UNSIGNED NOT NULL,
 first_name VARCHAR(100),
 last_name VARCHAR(100),
 position VARCHAR(100),
 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 FOREIGN KEY (address_id)  REFERENCES us_tAddress(id)
);

or ALTER it to us_tUser table

ALTER TABLE `us_tUser` MODIFY COLUMN address_id INT (6) UNSIGNED NOT NULL;

Add that to your table you can not insert data without data for address_id column. if you want to insert data without data for address_id column I recommend don't add this or add DEFAULT value to that column

Why the FOREIGN KEY doesn't work for the LOOP?

1 Answers1