Cannot add or update a child row: a foreign key constraint fails

Question

I'm trying to INSERT the notice_title and notice_content into the notices table and the category_type into the categories table but I get the following error below and can't work it out.

Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[23000]: Integrity constraint violation: 1048 Column 'category_type' cannot be null' in C:\xampp\htdocs\add_notice.php:17 Stack trace: #0 C:\xampp\htdocs\add_notice.php(17): PDOStatement->execute() #1 {main} thrown in C:\xampp\htdocs\add_notice.php on line 17

MySQL

CREATE TABLE `categories`
(
     `category_id` INT(3) NOT NULL AUTO_INCREMENT, 
     `category_type` VARCHAR(255) NOT NULL, 
      PRIMARY KEY (`category_id`)
)     ENGINE = InnoDB;

CREATE TABLE `notices`
(
     `notice_id` INT(3) NOT NULL AUTO_INCREMENT, 
     `notice_category_id` INT(3) NOT NULL, 
     `notice_user_id` INT(3) NOT NULL, 
     `notice_title` VARCHAR(255) NOT NULL, 
     `notice_content` VARCHAR(500) NOT NULL, 
     `notice_date` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, 
      PRIMARY KEY (`notice_id`), 
      FOREIGN KEY (`notice_category_id`)
           REFERENCES categories(`category_id`), 
      FOREIGN KEY (`notice_user_id`)
           REFERENCES users(`user_id`)
)     ENGINE = InnoDB;

add_notice.php

<?php
session_start();

include_once("database/connect.php");

if(isset($_POST['add_notice'])) {
    $notice_id      = $_POST['notice_id'];
    $notice_title   = $_POST['notice_title'];
    $notice_content= $_POST['notice_content'];
    $category_id    = $_POST['category_id'];
    $category_type  = $_POST['category_type'];
}

$query1 = "INSERT INTO categories (category_id, category_type) VALUES (:category_id, :category_type)";
$query1 = $connection->prepare($query1);
$query1->bindParam(":category_id", $_POST["category_id"]);
$query1->bindParam(":category_type", $_POST["category_type"]);
$query1->execute();

$query2 = "INSERT INTO notices (notice_id, notice_title, notice_content, notice_category_id) VALUES (:notice_id, :notice_title, :notice_content, :notice_category_id)";
$query2 = $connection->prepare($query2);
$query2->bindParam(":notice_id", $_POST["notice_id"]);
$query2->bindParam(":notice_title", $_POST["notice_title"]);
$query2->bindParam(":notice_content", $_POST["notice_content"]);
$query2->execute();
?>

<!DOCTYPE html>
<html lang="en">
<head>
    <title>Create a Notice</title>
</head>
<body>
    <h4>Create a Notice</h4>
        <form method="post" name="add_notice" action="add_notice.php">
            <input name="notice_id" hidden />
            <input name="category_id" hidden />

            <input type="text" name="notice_title" />
            <br />
            <textarea name="notice_content" /></textarea>
            <br /><br />
            <label>Category:</label>
                <select name="category_type">
                    <option value="">Select...</option>
                    <option value="Content1">Content1</option>
                    <option value="Content2">Content2</option>
                    <option value="Content3">Content3</option>
                </select>
            <br />
            <button type="submit" name="submit">Create a Notice</button>
        </form>
</body>

Thanks

so `categories.category_id` does not have a value to make what you have done valid. Notice that the first insert is where it fails. Perhaps the 2nd insert would have cleared things up, but that is just the order you have the code in — Drew, Nov 02 '15 at 01:11
Possible duplicate of: http://stackoverflow.com/questions/1253459/mysql-error-1452-cannot-add-or-update-a-child-row-a-foreign-key-constraint-fa — user2182349, Nov 02 '15 at 01:16
were you looking for an actual Answer, or do you realize what is going on now? — Drew, Nov 02 '15 at 01:24
I'm still a little confused and trying to work it out by what you said but "perhaps the 2nd insert would have cleared things up" not sure what you mean by that I've got it to add into the categories table the category_type but the notices table still gives the same error and doesn't add anything, sorry I'm new to programming — natluf, Nov 02 '15 at 01:30
just to let ya know, if you don't @drew me, I am not brought back here to see your message :) It is only out of stubborn curiosity that I am here — Drew, Nov 02 '15 at 01:47
Here is a simple way to debug it in my mind. Right before your first insert, do this: Echo out the value of **$query1**. That data does not exist in `categories.category_id`. It is that simple — Drew, Nov 02 '15 at 01:49
your form is screwed up. or your data is screwed up. the $_POST['category_id'] is not checked, is empty, etc, look at PHP isset() function — Drew, Nov 02 '15 at 01:54
the thing is here `name="add_notice"` forms do not hold the name attribute unless used with jQuery and I see no jQuery here. Plus, what's happening here is that those 2 POST arrays are most likely empty, just as @Drew posted in an answer given below. *"the inserts happen with blanks before the form is shown"*. You need to use `!empty()` against ALL your POST arrays. Either do that, or remove the constraints. Use error reporting. — Funk Forty Niner, Nov 02 '15 at 02:57
thanks Fred. I knew someone that knows their stuff ... it would be obvious — Drew, Nov 02 '15 at 02:58
@Drew Anytime Drew. I won't be posting an answer for this, fearing it will only open up a can of worms that I won't be able to put back in, and locked away in permanence. Edit: No, I think I will post my comment as an answer. Hold on.. I posted one. — Funk Forty Niner, Nov 02 '15 at 03:00
gotcha, wasn't gonna do an Answer anyway. I would have to vaguely know the Answer myself @Fred-ii- thanks again — Drew, Nov 02 '15 at 03:04
Anytime @Drew now to sit and wait in anticipation ;-) *Cheers* — Funk Forty Niner, Nov 02 '15 at 03:05
@Drew I noticed something else that's in play here. 4 binded values, but 3 in their bindParam, and using an AI'd bind; additional failure. Edit made to my answer. — Funk Forty Niner, Nov 02 '15 at 03:10
@Fred-ii- Could you please have a look at the code? and maybe suggest how I could make it simpler? it works! thanks — natluf, Nov 02 '15 at 10:41
@natluf You're welcome and am glad I could be of help. However, you modified your question to hold the code that I added in my answer and I had to do a rollback to a previous revision, You shouldn't do that, because people will see your question and tell themselves: *"He has the code right, so why the answer?"*. Where they may downvote my answer for it. If you have another question on how to make it better, then please post a new question http://stackoverflow.com/questions/ask - thank you. — Funk Forty Niner, Nov 02 '15 at 12:06
@Fred-ii- would I need to open a new topic for this? As I'm still trying to get the categories and notices to work? As before I did it with rowCount() and if the row is deleted it skips the id thats been delete so it goes like 1,2,3,5 I'm trying to this with lastInsetId(), but every time i refresh/load the page it adds another key to the database $category_id = ""; $query = $connection->prepare("INSERT INTO categories (category_id) VALUES (?)"); $query->bindValue(1, $category_id); if($query->execute()) { $notice_category_id = $connection->lastInsertId(); } — natluf, Nov 03 '15 at 03:10

Funk Forty Niner · Accepted Answer · 2015-11-02T12:15:50.203

Firstly, this name="add_notice". Forms do not hold the name attribute unless used with jQuery and I see no jQuery here.

Plus, what's happening here is that those 2 POST arrays are most likely empty, just as @Drew posted in an answer given below. "the inserts happen with blanks before the form is shown".

You need to use !empty() against ALL your POST arrays. Either do that, or remove the constraints and use a default value.

Use error reporting.

References:

Plus, you're also missing a bind here. You have 4 in your initial query, but only 3 where the binding is happening after preparing your query.

$query2 = "INSERT INTO notices (notice_id, notice_title, notice_content, notice_category_id) VALUES (:notice_id, :notice_title, :notice_content, :notice_category_id)";
$query2 = $connection->prepare($query2);
$query2->bindParam(":notice_id", $_POST["notice_id"]);
$query2->bindParam(":notice_title", $_POST["notice_title"]);
$query2->bindParam(":notice_content", $_POST["notice_content"]);
$query2->execute();

Since notice_id is AI, you need to remove it from your query and add the one for :notice_category_id which is missing here.

A major contributing reason as to why notice_title and notice_content are empty is this:

You will need to add values for the following and fixing the syntax error for "hidden" missing a "type":

<input name="notice_id" hidden />
<input name="category_id" hidden />

which should look something like this:

<input name="notice_id" type="hidden" value="example_value_x" />
<input name="category_id" type="hidden" value="example_value_y" />

Nota: value="example_value_x" and value="example_value_y" are representative values. You will need to fill that one in for its respective values.

As for which one is to be used for the :notice_category_id bind, is unknown. You will need to fill that one in for its respective value.

I believe I have given you enough to make your code work. It's up to you now, to fill in some of the blanks.

score 0 · Answer 2 · answered Nov 02 '15 at 01:30

0

I think u should insert categories first berore inserting notices.

When u insert notice, there is no category it references to, so constraint works.

answered Nov 02 '15 at 01:30

userlond

3,632
2
36
53

I tried that and I think Drew was hinting that but I still get the same error but on line 20 now – natluf Nov 02 '15 at 01:31
Check your categories query. Is it working at all? Does it insert data into categories table? – userlond Nov 02 '15 at 01:36
yep it adds a category into the categories table but I had to make an input field for notice_category_id and enter an INT which isn't in the database field which I was hoping it would do it automatically and now I get an error on line 20 but for notice_user_id and user_id – natluf Nov 02 '15 at 01:43
It means notice can't reference to according user row. So constraint violation raises. You should specify user foreing key when insert notice. – userlond Nov 02 '15 at 01:46
@Drew okay so I changed my code and now I get category_type cannot by null but I am selecting from the dropbox. I updated the main post – natluf Nov 02 '15 at 02:36
1

your php file has the form on the bottom, posts back to self, but the first time the php file is loaded, presumably to show the form, it does an if statement but then right after that it does two inserts without the posts set. Let me say it another way: the inserts happen with blanks before the form is shown – Drew Nov 02 '15 at 02:50

Cannot add or update a child row: a foreign key constraint fails

2 Answers2