Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Can't insert foreign key value into linking table

I am currently trying to insert data into a table called "customer_quote", this table acts as a linking table between the "customer" table and the "customer_tariffs" table. It also records the user who sumbitted the data via the "user" table.

Here is a schema of my db:

http://i.imgur.com/LOG1T.png enter image description here

and here is a screenshot of the table that is not allowing me to insert into it.

http://i.imgur.com/i2wiU.png

enter image description here

This is the process in how I insert into my db:

  1. Insert data into customer table
  2. Retrieve row id using mysql_insert_id
  3. Insert data into customer_quote <--- Not working!

Here is the code:

    //code above this inserted data into customer table

//get id of row where data was just inserted
$sustomer->cid = mysql_insert_id($db);

//insert into customer_quote table
$database->query("INSERT INTO customer_quote (cid)
        Values ('$customer->cid')");

** New Error message**

'Cannot add or update a child row: a foreign key constraint fails (quote_system.customer_quote, CONSTRAINT fk_customer_quote_customer FOREIGN KEY (cid) REFERENCES customer (id) ON DELETE NO ACTION ON UPDATE NO ACTION)'

As you can see that error feedback is useless , so after about three hours of testing I have concluded that the problem is my "cid" column in the "customer quote" table.

It only accepts certain values however my own php variable has the correct value which is available to insert via phpmyadmin as you can see in the screenshot below.

http://i.imgur.com/eEFou.png

enter image description here

So it has to be the constraints or something else in my table that is stopping me?

Any ideas how to solve this.

Thanks!

like image 939
tomaytotomato Avatar asked Jan 04 '12 14:01

tomaytotomato


2 Answers

I really hope its a simple typo in your question but your query isn't correct :

$database->query("INSERT INTO customer_quote (cid)
        Values ('$customer->cid'");

should be

$database->query("INSERT INTO customer_quote (cid)
        Values ('$customer->cid')"); // added closing bracket on values
like image 78
Manse Avatar answered Sep 29 '22 23:09

Manse


You have misplaced the double quote and missed a parens

Change the lines:

$database->query("INSERT INTO customer_quote (cid)
    Values ('$customer->cid'");

to

$database->query("INSERT INTO customer_quote (cid)
    Values ('$customer->cid')");
like image 39
Toto Avatar answered Sep 30 '22 00:09

Toto