CODEWITHSUNDEEP
mysqllaravellaravel-8
vandettadyckies
vandettadyckies

Reputation: 101

SQLSTATE[23000]: Integrity constraint violation: 4025 CONSTRAINT

Hey I have problem when I insert my data from laravel to MySQL with error SQLSTATE[23000]: Integrity constraint violation: 4025 CONSTRAINT

Here is store controller

public function store(Request $request)
{
    // $faker = Faker::create('id_ID');
    $image = $request->file('Product_photo');
    $extension = $image->getClientOriginalExtension();
    $name = $request->Name;
    $file_name = $name.'.'.$extension;
    $image->move("images/listcar/", $file_name);
    DB::table('car')
    ->insert([
        //'id' => $faker->unique()->numberBetween($min = 0, $max = 200),
        'product_photo' => $file_name,
        'name' => $request->Name,
        'production_year' => $request->Production_year,
        'price' => $request->Price,
        'merk' => $request->Merk,
        'description' => $request->Description,
        'varian' => $request->Variant,
        'machine' =>$request->Machine,
        'transmision' => $request->Transmision,
        'wheel' => $request->Wheel,
    ]);
    return redirect('/admin');

Here is my blade

<body>
<h1>Tambah Mobil</h1>
<div class="container">
    <form action="/car/add" method="post" enctype="multipart/form-data">
    {{ csrf_field() }}
    <th>Nama</th><br>
        <input type="text" name="Name" value=""><br/><br>
    <th>Tahun Produksi</th><br>
        <input type="text" name="Production_year" value=""><br/><br>
    <th>Harga</th><br>
        <input type="number" name="Price" value=""><br/><br>
    <th>Select Product Image</th><br>
        <input type="file" name="Product_photo" /><br/><br>
    <th>Merek</th><br>
        <input type="text" name="Merk" value=""><br/><br>
    <th>Deskripsi</th><br>
        <input type="textarea" name="Description" value=""><br/><br>
    <th>Varian</th><br>
        <input type="text" name="Variant" value=""><br/><br>
    <th>Mesin</th><br>
        <input type="text" name="Machine" value=""><br/><br>
    <th>Transmisi</th><br>
        <input type="text" name="Transmision" value=""><br/><br>
    <th>Penggerak Roda</th><br>
        <input type="text" name="Wheel" value=""><br/><br>
    <br>
        <input class="btn btn-primary" type="submit" value="Tambah Data Mobil">
    </form>
    <br>
    <br>
</div>

MySQL structure enter image description here

I really dont know where I went wrong, so please tell me if there is a wrong concepts

Upvotes: 5

Views: 18841

Answers (9)

Khurram Qadeer
Khurram Qadeer

Reputation: 29

Did you make the connection_discord and columns nullable? These values are not set right now and could cause issues because of that.

Also, are these columns of the type JSON in your migration

Upvotes: 0

Khurram Qadeer
Khurram Qadeer

Reputation: 29

I have updated the collation for the MySQL table and for some tables only I have updated the column charset

utf8mb4_general_ci

OR

utf8mb3_general_ci

Upvotes: 0

Adeel Raza Azeemi
Adeel Raza Azeemi

Reputation: 823

I got the same error because I used a JSON field. and while inserting values in that field (which must be a valid JSON format); I got an error in that format (basically missed the close double quotes).

After correcting that it works like a charm.

Upvotes: 0

Macke
Macke

Reputation: 383

This error may also occur in case a field that is not nullable and has no default value is left without value.

To fix this either add default values in your code or in your migraitons.

Here is a migration sample that updates email field to nullable and sets price default value to zero:

<?php

Schema::table('users', function (Blueprint $table) {
    $table->string('email', 255)->nullable()->change();
    $table->integer('price')->default(0)->change();
});

https://laravel.com/docs/8.x/migrations#column-modifiers

Upvotes: 0

dsofeir
dsofeir

Reputation: 1

I recently encountered a Laravel 'constraint violation' error related to JSON field. The logs were not revealing and the bug was intermittent. It took me some time, but fortunately the fix was easy.

TL;DR

  • Problem: In Laravel's Model class I had set the field to guarded and then provided an invalid default value.
  • Solution: In Laravel make the default values of your model's JSON fields valid JSON encoded strings. Even when they are empty {}.

The Error

When I deployed code to the production environment for the first time, I saw a HTTP-500 error which had not arisen in my local testing:

[2022-09-27 03:54:27] local.ERROR: SQLSTATE[23000]: Integrity constraint violation: 
4025 CONSTRAINT `table.field3` failed for `database`.`table` (SQL: insert into `table` (`field1`, `field2`, `field3`
...
#0 /path/to/project/vendor/laravel/framework/src/Illuminate/Database/Connection.php(719): 
Illuminate\\Database\\Connection->runQueryCallback('insert into `ta...', Array, Object(Closure))

Not a very illuminating (pun intended) log entry, plus I could not reproduce the error in my vagrant development environment. The only difference between the vagrant box and production was that MariaDB was version 10.9 versus version 10.7 in production.

The Problem

It turns out the field type being json() in the Laravel migration was significant.

database/migrations/2022_09_25_133630_create_model_table.php :

new class extends Migration
{
    public function up()
    {
        Schema::create('my_model', function (Blueprint $table) {
            $table->id();
            ...
            $table->json('field3');

I had always wondered why Laravel bothered with so many field types in the migration when they all got reduced to a handful once the SQL tables were created. In this instance the MariaDB column was longtext (non-nullable).

In Laravel's Model class I had set the field to guarded and then provided a default value which I thought was appropriate.

app/Models/my_model.php :

class my_model extends Model
{
    use HasFactory;

    /**
     * The attributes that are NOT mass assignable.
     *
     * @var string[]
     */
    protected $guarded = [
        'field3'
    ];

    /**
     * Default values for attributes.
     *
     * @var array
     */
    protected $attributes = [
        'field3' => "",
    ];

It must be that the underlying mechanism of how the field was stored across the two versions of MariaDB came to the fore here? The error had arisen because an empty string which I had provided as a default value is not a valid JSON encoding and thus not compatible with the Laravel field type of json(). In my defense it is perfectly valid in the context of a non-nullable longtext column in MariaDB.

The Solution

In Laravel make the default values of your model's JSON fields valid JSON encoded strings.

app/Models/my_model.php :

class my_model extends Model
{
    use HasFactory;

    /**
     * The attributes that are NOT mass assignable.
     *
     * @var string[]
     */
    protected $guarded = [
        'field3'
    ];

    /**
     * Default values for attributes.
     *
     * @var array
     */
    protected $attributes = [
        'field3' => "{}",
    ];

Upvotes: 0

Amala
Amala

Reputation: 1

$user_role = array(('role')=>json_encode($request['role']));
$user_role = implode(', ', $user_role);

Upvotes: -1

saber tabatabaee yazdi
saber tabatabaee yazdi

Reputation: 4959

i had same problem and solved:

if you save non-json into JSON field this error occurred like this:

    $item = new WhatsAppWebhook($data);
    $item->save();
    return $item;

you need to encode

    $payload = array("payload"=>json_encode($data));
    $item = new WhatsAppWebhook($payload);
    $item->save();
    return $item;

Upvotes: 6

pubudu sachintha
pubudu sachintha

Reputation: 763

Change collation on mysql table - utf8mb4_general_ci : )

Upvotes: 12

Matthew
Matthew

Reputation: 309

Check you model $fillable array. Ensure all columns that records will be inserted are part of your $fillable

Upvotes: 0

Related Questions