Level 102
The query you see in the error isn't the actual query. It's a simple approximation as pdo has no way of showing the actual query (as it uses bound parameters)
Maybe your editor has a more lax approach to strict mode or foreign keys
Jul 18, 2022
19
Level 1
String delimiters missing in INSERT query
Hi, I have a problem while inserting rows in a table:
Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails (test_db.documents_rows, CONSTRAINT fk_dro_vat FOREIGN KEY (vat_type_id) REFERENCES vat_types (id`) ON DELETE NO ACTION ON UPDATE NO ACTION)
insert into `documents_rows` (`document_id`, `product_id`, `product_name`, `vat_type_id`, `created_by`, `updated_at`, `created_at`)
values (4, 1, prodotto 1, 1, 1, 2022-07-18 18:33:24, 2022-07-18 18:33:24)
It's not a problem with the foreign key: if I add the missing delimiters, the query works fine:
insert into `documents_rows` (`document_id`, `product_id`, `product_name`, `vat_type_id`, `created_by`, `updated_at`, `created_at`)
values (4, 1, 'prodotto 1', 1, 1, '2022-07-18 18:33:24', '2022-07-18 18:33:24')
The Model:
<?php
namespace App\Models;
use Illuminate\Database\Eloquent\Model;
/**
* @property integer $id
* @property integer $document_id
* @property integer $product_id
* @property integer $vat_type_id
* @property string $product_name
* @property string $created_at
* @property string $updated_at
* @property integer $created_by
* @property Document $document
* @property VatType $vatType
* @property Product $product
*/
class DocumentsRow extends Model
{
/**
* @var array
*/
protected $fillable = ['document_id', 'product_id', 'vat_type_id', 'product_name', 'created_at', 'updated_at', 'created_by'];
/**
* @return \Illuminate\Database\Eloquent\Relations\BelongsTo
*/
public function document()
{
return $this->belongsTo('App\Models\Document');
}
/**
* @return \Illuminate\Database\Eloquent\Relations\BelongsTo
*/
public function vatType()
{
return $this->belongsTo('App\Models\VatType');
}
/**
* @return \Illuminate\Database\Eloquent\Relations\BelongsTo
*/
public function product()
{
return $this->belongsTo('App\Models\Product');
}
}
The Migration:
<?php
use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;
class CreateDocumentsRowsTable extends Migration
{
/**
* Run the migrations.
*
* @return void
*/
public function up()
{
Schema::create('documents_rows', function (Blueprint $table) {
$table->integer('id', true);
$table->integer('document_id')->default(0)->index('idx_document_id');
$table->integer('product_id')->default(0)->index('idx_product_id');
$table->string('product_name', 50);
$table->integer('vat_type_id')->default(0)->index('idx_vat_type_id');
$table->timestamp('created_at')->nullable()->useCurrent();
$table->timestamp('updated_at')->useCurrentOnUpdate()->nullable();
$table->integer('created_by')->default(0);
});
}
/**
* Reverse the migrations.
*
* @return void
*/
public function down()
{
Schema::dropIfExists('documents_rows');
}
}
I'm stuck. :-(
Level 102
Just saw your migration which has no foreign keys set? Did you add them manually?
Level 1
@Sinnbeck foreign keys are in a separate file:
<?php
use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;
class AddForeignKeysToDocumentsRowsTable extends Migration
{
/**
* Run the migrations.
*
* @return void
*/
public function up()
{
Schema::table('documents_rows', function (Blueprint $table) {
$table->foreign(['document_id'], 'fk_dro_doc')->references(['id'])->on('documents')->onUpdate('NO ACTION')->onDelete('NO ACTION');
$table->foreign(['vat_type_id'], 'fk_dro_vat')->references(['id'])->on('vat_types')->onUpdate('NO ACTION')->onDelete('NO ACTION');
$table->foreign(['product_id'], 'fk_dro_prd')->references(['id'])->on('products')->onUpdate('NO ACTION')->onDelete('NO ACTION');
});
}
/**
* Reverse the migrations.
*
* @return void
*/
public function down()
{
Schema::table('documents_rows', function (Blueprint $table) {
$table->dropForeign('fk_dro_doc');
$table->dropForeign('fk_dro_vat');
$table->dropForeign('fk_dro_prd');
});
}
}
Level 1
@Sinnbeck Ah... thank you for your response
Level 102
@robman70 can you show the output of dd(\DB::table('vat_types')->where('id', 1)->first()); if you add it just before the code that fails?
Level 1
^ {#1807 ? +"id": 1 +"name": "iva 22%" +"rate": 22.0 +"created_at": "2022-07-18 12:49:49" +"updated_at": null +"created_by": 0 +"updated_by": 0 }
Level 102
@robman70 very strange. Can you show the code that fails perhaps?
Level 1
@Sinnbeck Thank you again...
$documentRow->document_id = $doc[0]->docID;
$documentRow->product_id = $orderRow->product_id;
$documentRow->product_name = $orderRow->product_name;
$documentRow->vat_type_id = $vat_type_id;
$documentRow->created_by = auth()->guard('web')->user()->id;
The remaining columns have default values.
There are something wrong?
Just before this I have another block of code that inserts into another table with string fields and foreign keys and it works without any problems.
Level 10
@robman70 It seems fine but without knowing where $vat_type_id comes from is hard to say. Is $documentRow a new instance of that model ? What does dd($documentRow->getAttributes()) show ?
Level 102
@robman70 there must be something you aren't showing that is causing the problem, but it's impossible to see or recreate from what you have shown
Level 1
@psrz Thank you for your response.
^ array:12 [▼
"document_id" => 17
"product_id" => 1
"product_name" => "prodotto 1"
"vat_type_id" => 1
"created_by" => 1
]
Level 1
@Sinnbeck You are right. I wrote because I believed that the lack of delimiters was the problem ... at this point I have to get by on my own, thank you very much for the support and for the time you spent
Level 102
@robman70 happy to help. If you get closer to the issue and need help be sure to let us know. Or if you can make a tiny reproduction on github we can test ourselves :)
Level 73
You try to insert 8 values when there should only be 7. The product name needs to be passed as a string, you need to show us your store method.
Level 1
@Tray2 Thank you for your response, but I see:
7 columns (in the query): 'document_id', 'product_id', 'vat_type_id', 'product_name', 'created_at', 'updated_at', 'created_by'
7 values (in the query): 4, 1, prodotto 1, 1, 1, 2022-07-18 18:33:24, 2022-07-18 18:33:24
7 Fields (in the Model) and 8 (in the Migration), but the 'id' column is AUTO_INCREMENT
Maybe i missed something?
Level 35
What database are you using? Is there a reason you define your own indexes?
Your code looks ok except that you should not set ->default(0) if you also set a foreign key to a field that is an auto increment field. As the auto increment normally starts at 1 so the default of 0 will never be a valid value. It should be ->nullable() if you want the field to be empty or you should remove the ->default(0).
It is also not necessary to manually set an index if you also set a foreign key in MySql InnoDB.
MySQL requires that foreign key columns be indexed; if you create a table with a foreign key constraint but no index on a given column, an index is created. https://dev.mysql.com/doc/refman/8.0/en/constraint-foreign-key.html
Level 1
@click Thank you for your response, you're right, there some little errors but the problem is another
Level 1
Thanks to all. I finally find the solution: there was a problem in the vat_types table. The trouble is that I didn't understand exactly what the problem was: I recreated the table from scratch, with the same fields, defined in the same way, same indexes, etc... and now everything works fine. Previously I had imported this table from another DB. There was probably something wrong but not visible in the table structure (in fact the new table, despite being identical and with the same records, has a completely different size: 4 times more great). I had never seen such a thing but in any case I solved it.
Thanks again to everyone for the time you have dedicated to my problem.
Level 102
@robman70 happy to have helped :)
Please or to participate in this conversation.