PDO: Integrity constraint violation (UPDATE TABLE)

Question

First Table:

CREATE TABLE Portfolio_Categories 
(
  cat_id int(11) NOT NULL,
  cat_title varchar(255) NOT NULL,
  cat_dir varchar(255) NOT NULL
) 
ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=latin1;

Second Table:

CREATE TABLE Portfolio_Images 
(
  img_id int(11) NOT NULL,
  cat_id int(11) NOT NULL,
  img varchar(255) NOT NULL,
  img_title varchar(255) DEFAULT NULL
) 
ENGINE=InnoDB AUTO_INCREMENT=21 DEFAULT CHARSET=latin1 COMMENT='Table to store the Portfolio Images';

Constraints:

ALTER TABLE Portfolio_Categories
  ADD PRIMARY KEY (cat_id);

ALTER TABLE Portfolio_Images
  ADD PRIMARY KEY (img_id), ADD KEY cat_id (cat_id), ADD KEY img_id (img_id);

ALTER TABLE Portfolio_Images ADD CONSTRAINT cat_id FOREIGN KEY
    (cat_id) REFERENCES Portfolio_Categories (cat_id);

My PHP Code:

$query =   "UPDATE
                Portfolio_Images
            SET
                Portfolio_Images.img = :new_img,
                Portfolio_Images.img_title = :new_tit,
                Portfolio_Images.cat_id =
                    (SELECT t_cat.cat_id FROM (SELECT * FROM Portfolio_Categories) AS t_cat WHERE t_cat.cat_title = :new_cat)
            WHERE
                Portfolio_Images.img = :old_img;";
$stmt = $_MySQLConn->prepare($query);

$stmt->bindParam(':new_img', $new_img);
$stmt->bindParam(':new_tit', $new_tit);
$stmt->bindParam(':new_cat', $new_cat);
$stmt->bindParam(':old_img', $old_img);

if($stmt->execute())
{
    $return_value = array('success'=>true,
                          'new_img:'=>$new_img,
                          'new_tit'=>$new_tit,
                          'new_cat'=>$new_cat,
                          'old_img'=>$old_img);
}
else
{
    $return_value = array('success'=>false,'error_code'=>'Could not execute query');
}

What should it do:

It should update my table without any error (as it does if I run the statement directly)

What does it do:

PHP shows me this error message:

SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails (1_new.Portfolio_Images, CONSTRAINT cat_id FOREIGN KEY (cat_id) REFERENCES Portfolio_Categories (cat_id))

possible duplicate of [Integrity constraint violation: 1452 Cannot add or update a child row:](http://stackoverflow.com/questions/14063652/integrity-constraint-violation-1452-cannot-add-or-update-a-child-row) — Spechal, Mar 23 '15 at 15:56

score 1 · Accepted Answer · answered Mar 23 '15 at 16:49

This means that you are inserting a row in Portfolio_Images where the value in Portfolio_Images.cat_id does not exist in Portfolio_Categories.cat_id.

In other words, if in Portfolio_Categories.cat_id you only have the values 1, 2, 4 you can only insert in Portfolio_Images.cat_id the values 1, 2 or 4.

PDO: Integrity constraint violation (UPDATE TABLE)

1 Answers1