+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: CodeIgniter 4 (https://forum.codeigniter.com/forumdisplay.php?fid=28)
+--- Forum: CodeIgniter 4 Support (https://forum.codeigniter.com/forumdisplay.php?fid=30)
+--- Thread: Cannot add or update a child row: a foreign key constraint fails (/showthread.php?tid=82067)
Pages:
1
2
Cannot add or update a child row: a foreign key constraint fails - Sincere - 06-07-2022
I'm getting the following error when inserting data (through the required model) into our SQL database:
Cannot add or update a child row: a foreign key constraint fails (`scs`.`live_outputs`, CONSTRAINT `live_outputs_ibfk_1` FOREIGN KEY (`livestream_id`) REFERENCES `live_streams` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT)
I'm well aware what this means: that I'm referencing to non existing or incorrect data on the parent field (in this case the ID field of the live_streams table must match livestream_id). However I'm 100% sure that the ID submitted in the form is correct and matches the database:
As for the database:
Any suggestions as to what I might need to look at?
RE: Cannot add or update a child row: a foreign key constraint fails - iRedds - 06-08-2022
Check if the fields in $allowedFields match.
Try adding the same data via a raw query.
RE: Cannot add or update a child row: a foreign key constraint fails - Sincere - 06-09-2022
(06-08-2022, 02:07 PM)iRedds Wrote: Check if the fields in $allowedFields match.
Try adding the same data via a raw query.
Works fine if I run the query manually through PHPmyadmin.
Model looks fine to me:
Code:
{
protected $DBGroup = 'default';
protected $table = 'live_outputs';
protected $primaryKey = 'id';
protected $useAutoIncrement = true;
protected $returnType = 'object';
protected $useSoftDeletes = true;
protected $allowedFields = ['alias','status','description','livestream_id,','type','secret','max_resolution'];
protected $useTimestamps = true;
protected $createdField = 'created_at';
protected $updatedField = 'updated_at';
protected $deletedField = 'deleted_at';
protected $validationRules = [
'alias' => 'required|min_length[4]|max_length[16]|alpha_numeric|is_unique[live_outputs.alias]',
'status' => 'numeric',
'description' => 'required|max_length[40]',
'livestream_id' => 'required|numeric',
'type' => 'required|max_length[8]',
'secret' => 'permit_empty|max_length[16]',
'max_resolution' => 'permit_empty|numeric',
];
protected $skipValidation = false;
}PHP Code:
public function test_insert()
{
$data= [
'alias' => 'AbCdEfGh',
'status' => 0,
'description' => 'Manual SQL Test',
'livestream_id' => 5,
'type' => 'public'
];
$db= \Config\Database::connect();
$builder = $db->table('live_outputs');
$builder->insert($data);
}
So apparently something goes wrong when using the model/controller:
PHP Code:
public function add()
{
$authorize = service('authorization');
$LiveOutputModel = new LiveOutputModel();
$LiveModel = new LiveModel();
//removed some authentication code
if(!$this->can_add_live()){return(\CodeIgniter\Exceptions\PageNotFoundException::forPageNotFound());}
if($_SERVER['REQUEST_METHOD'] == 'POST') {
$_POST['alias'] = $this->getKey(8);
$LiveOutputModel->insert($_POST);
if($LiveOutputModel->errors()==null){
return(redirect()->to('/panel/live/outputs/'));
}
// load view, helper etc.
}
RE: Cannot add or update a child row: a foreign key constraint fails - Sincere - 06-14-2022
I tried something different today:
PHP Code:
public function man_insert2()
{
$data= [
'alias' => 'AbCxExGh',
'status' => 0,
'description' => 'Manual SQL Test',
'livestream_id' => 5,
'type' => 'public'
];
$LiveOutputModel = new LiveOutputModel();
print_r($data);
$LiveOutputModel->insert($data);
}
Unfortunately still the same error, oddly enough the array is exactly the same as with the manual SQL query. So clearly there's something wrong when using the model to insert the data:
![[Image: Screen-Shot-2022-06-14-at-20-06-58.png]](https://i.ibb.co/BsphrtL/Screen-Shot-2022-06-14-at-20-06-58.png)
Again, I'd greatly appreciate any input on this matter!
RE: Cannot add or update a child row: a foreign key constraint fails - kenjis - 06-14-2022
Check the SQL string that the Model executes.
RE: Cannot add or update a child row: a foreign key constraint fails - Sincere - 06-14-2022
I would like to, how can I check this? The above screenshot is what I get when I test this.
RE: Cannot add or update a child row: a foreign key constraint fails - kenjis - 06-14-2022
Add the following code in app/Config/Events.php, and check the log file.
PHP Code:
Events::on(
'DBQuery',
static function (Query $query) { log_message('alert', (string) $query); }
);
RE: Cannot add or update a child row: a foreign key constraint fails - Sincere - 06-14-2022
I'm missing something to get this to work in my config/events.php file:
PHP Code:
<?php
namespace Config;
use CodeIgniter\Events\Events;
use CodeIgniter\Exceptions\FrameworkException;
/*
* --------------------------------------------------------------------
* Application Events
* --------------------------------------------------------------------
* Events allow you to tap into the execution of the program without
* modifying or extending core files. This file provides a central
* location to define your events, though they can always be added
* at run-time, also, if needed.
*
* You create code that can execute by subscribing to events with
* the 'on()' method. This accepts any form of callable, including
* Closures, that will be executed when the event is triggered.
*
* Example:
* Events::on('create', [$myInstance, 'myMethod']);
*/
Events::on('pre_system', static function () {
if (ENVIRONMENT !== 'testing') {
if (ini_get('zlib.output_compression')) {
throw FrameworkException::forEnabledZlibOutputCompression();
}
while (ob_get_level() > 0) {
ob_end_flush();
}
ob_start(static function ($buffer) {
return $buffer;
});
}
/*
* --------------------------------------------------------------------
* Debug Toolbar Listeners.
* --------------------------------------------------------------------
* If you delete, they will no longer be collected.
*/
if (CI_DEBUG && ! is_cli()) {
Events::on('DBQuery', 'CodeIgniter\Debug\Toolbar\Collectors\Database::collect');
Services::toolbar()->respond();
}
if (DBQuery) {
static function (Query $query) { log_message('alert', (string) $query);};
}
});
RE: Cannot add or update a child row: a foreign key constraint fails - kenjis - 06-14-2022
Remove this:
PHP Code:
if (DBQuery) {
static function (Query $query) { log_message('alert', (string) $query);};
}
And add the exact code I pasted in the end of the file.
RE: Cannot add or update a child row: a foreign key constraint fails - Sincere - 06-14-2022
I'm getting this error now:
Code:
CodeIgniter\Events\Events::Config\{closure}(): Argument #1 ($query) must be of type Config\Query, CodeIgniter\Database\Query givenThis is my events.php:
PHP Code:
<?php
namespace Config;
use CodeIgniter\Events\Events;
use CodeIgniter\Exceptions\FrameworkException;
Events::on('pre_system', static function () {
if (ENVIRONMENT !== 'testing') {
if (ini_get('zlib.output_compression')) {
throw FrameworkException::forEnabledZlibOutputCompression();
}
while (ob_get_level() > 0) {
ob_end_flush();
}
ob_start(static function ($buffer) {
return $buffer;
});
}
if (CI_DEBUG && ! is_cli()) {
Events::on('DBQuery', 'CodeIgniter\Debug\Toolbar\Collectors\Database::collect');
Services::toolbar()->respond();
}
});
Events::on(
'DBQuery',
static function (Query $query) { log_message('alert', (string) $query); }
);