Foreign Key Failure in MySQL

Question

I have created a database composed of three tables. This is my query in creating my tables with Foreign Key.

CREATE TABLE reporter
(
  reporterid INT NOT NULL AUTO_INCREMENT,
  firstname VARCHAR(1000) NOT NULL,
  lastname VARCHAR(100) NOT NULL,
  PRIMARY KEY (reporterid)
);

CREATE TABLE flood
(
  floodid INT NOT NULL AUTO_INCREMENT,
  address VARCHAR(500) NOT NULL,
  description VARCHAR(1000) NOT NULL,
  dateofflood DATE NOT NULL,
  timeofflood INT NOT NULL,
  PRIMARY KEY (floodid)
);

CREATE TABLE reports
(
  reportid INT NOT NULL AUTO_INCREMENT,
  timereport NODATATYPE NOT NULL,
  datereport DATE NOT NULL,
  rid INT NOT NULL,
  fid INT NOT NULL,
  PRIMARY KEY (reportid),
  FOREIGN KEY (rid) REFERENCES reporter(reporterid),
  FOREIGN KEY (fid) REFERENCES flood(floodid)
);

I created a system in order for me to add records/row on my database through PHP. This is my code:

<?php

mysql_connect("localhost", "root", "") or die("Connection Failed");
mysql_select_db("flooddatabase")or die("Connection Failed"); 


$description = $_POST['description'];
$address = $_POST['address']; // Make sure to clean the
$dateofflood=$_POST['dateofflood'];
$timeofflood=$_POST['timeofflood'];
$firstname=$_POST['firstname'];
$lastname=$_POST['lastname'];
$dateofreport=$_POST['dateofreport'];

$timeofreport=$_POST['timeofreport'];

$query = "INSERT into flood(address,description,dateofflood,timeofflood) values ('$address','$description','$dateofflood','$timeofflood')";
$query2 = "INSERT into reporter(firstname,lastname) values ('$firstname','$lastname')";
$query3 = "INSERT into reports(dateofreport,timeofreport) values ('$dateofreport','$timeofreport')";



if(mysql_query($query))
  if(mysql_query($query2))
   if(mysql_query($query3))
        {
          echo "";
        } else
        {
          echo "fail";
        }

?>

The result that I am getting is fine. It's just that, in my REPORTS table, there is no foreign key that is being generated. For example I input something on my reporter table and flood table, the foreign key 'rid' and 'fid' has no values that references to both tables. Need help thank you.

The last insert statement expects values of `rid` and `fid` field. — 1000111, Feb 26 '16 at 13:27
The last insert, you're saying you want to insert into 2 columns but only define one column, which isn't going to work. — Jonnix, Feb 26 '16 at 13:30
Alter the primary keys of `flood` and `reporter` table to `AUTO_INCREMENT`. It's one of your tasks. — 1000111, Feb 26 '16 at 13:31
As an aside, mysql_* functions are deprecated in later PHP5 versions and removed in 7. Also, you're open to SQL injections attacks. Have a look at PDO / mysqli and prepared statements. — Jonnix, Feb 26 '16 at 13:32
@Subrata Dey Pappu Yes, I already have my floodid and reporterid auto_incremented... — user3767918, Feb 26 '16 at 13:33
@SubrataDeyPappu Updated it. In my local DB here, I have it auto incremented — user3767918, Feb 26 '16 at 13:34

score 0 · Answer 1 · answered Feb 26 '16 at 13:40

Get the just inserted Primary key value from flood table insert query. And store it to a variable say $f_id;
Get the just inserted primary key value from reporter table insert query and store it to a variable say $r_id;
Now Make your last insert statement like below:

"INSERT into reports(dateofreport,timeofreport,rid,fid) values ('$dateofreport','$timeofreport',$r_id,$f_id)";

I am not giving you a direct copy paste solution.

If you need to know how to get the last inserted id by executing an insert query then look at this link

I'll try this. But I have a question, in my previous experience, I had two tables one with FK and then in my query, I did not put any value to my FK but in my database, there's actually a record or value in my FK from my PK. — user3767918, Feb 26 '16 at 14:49

score 0 · Answer 2 · edited May 23 '17 at 11:50

0

there is no foreign key that is being generated

I'm not entirely sure what you even mean by that. Foreign keys aren't "generated". Primary keys can be, which you do:

reporterid INT NOT NULL AUTO_INCREMENT

(as well as for your other two tables)

the foreign key 'rid' and 'fid' has no values

Well, look at your query:

INSERT into reports(dateofreport,timeofreport) values ...

Where do you insert values for rid and fid? I'm actually pretty surprised this query works at all, since those columns don't allow NULL values:

rid INT NOT NULL,
fid INT NOT NULL,

(Though your column names also don't line up, so I find it likely that the code you're showing isn't actually the code you're using...) That point aside however, the fact still remains that if you want a value in those fields then you have to put a value in those fields:

INSERT into reports(dateofreport,timeofreport,rid,fid) values ...

After each query, you can get the last generated identifier from mysql_insert_id():

$last_id = mysql_insert_id();

Use that to then populate the values being inserted as foreign keys in subsequent queries.

Also worth noting, the mysql_* libraries are long since deprecated and have been replaced with mysqli_ and other libraries such as PDO. I highly recommend you upgrade to a current technology, since what you're using isn't supported by any vendor.

Additionally, and this is very important, your code is wide open to SQL injection attacks. This basically means that you execute any code your users send you. You should treat user input as values, not as executable code. This is a good place to start reading on the subject, as is this.

edited May 23 '17 at 11:50

Community

1
1

answered Feb 26 '16 at 13:41

David

208,112
36
198
279

The query1 and query2 works but the query3 doesn't. There's no rows added. (regarding what you said, " I'm actually pretty surprised this query works at all" – user3767918 Feb 26 '16 at 14:47
@user3767918: That would be something worth mentioning in the question. As worded, you're implying that a row is added but those values aren't present. If no row is added at all, then the query is definitely failing. Note that this code doesn't check for error messages from queries, it just assumes success. I bet the database is telling you that `NULL` values aren't allowed in those columns. – David Feb 26 '16 at 14:49
I'll try this. But I have a question, in my previous experience, I had two tables one with FK and then in my query, I did not put any value to my FK but in my database, there's actually a record or value in my FK from my PK. – user3767918 Feb 26 '16 at 14:50
@user3767918: I can't really comment on code you're not showing. But the bottom line is that if you want a field to have a value then you need to put a value in that field. – David Feb 26 '16 at 14:50
sorry can i ask again, what do you mean by this "Use that to then populate the values being inserted as foreign keys in subsequent queries. " – user3767918 Feb 26 '16 at 15:18
@user3767918: First you'd insert into `reporter`, then you'd get the ID which was generated from that insert, then you'd use that ID when inserting into `reports`. (Same for getting the ID from `flood`.) Your `reports` table requires values for `rid` and `fid`, so those are the values you need to insert into those fields. – David Feb 26 '16 at 15:20

Foreign Key Failure in MySQL

2 Answers2