Laravel 8 – CAN delete but CANNOT UPDATE parent row due to integrity constraints violation: foreign key constraint fails

I have a truck table, a package table, and a postman table. Truck table and package table has a one to many relationship. Truck table and postman table has a one to one relationship.

Now I can delete truck table just fine, but I got integrity constraints violation error inside the truck table when I try to update postman_name on truck data that has packages assigned to it. I don’t get the error when I try to change other non-foreign key properties or when I try to change postman_name on a truck data that currently has zero package assigned.

I only got the error when I try to update postman_name on a truck_number with packages assigned to it.

How can I fix this error?

the postman form inside truck/edit.blade.php :

<div class="form-group">
                <label for="postman_name">Postman in Charge</label>
                <select name="truck_number" class="form-control">
                <option selected disabled>-</option>
                @foreach ($postmen as $count => $postman)
                  <option value="{{$postman['postman_name']}}">{{$postman['postman_name']}}</option>
                @endforeach
                </select>
</div>

Truck update controller:

public function update(Request $request, $truck_id)
    {
        $request->validate([
            'truck_number'=>'required|unique:trucks,truck_id',
            'postman_name',
            'date_of_operation'=>'required'
        ]);
        $trucks = Truck::find($truck_id);
        $trucks->truck_number =  $request->get('truck_number');
        $trucks->postman_name = $request->get('postman_name');
        $trucks->date_of_operation = $request->get('date_of_operation');
        $trucks->save();
        return redirect(TRUCK)->with('success', 'Truck Updated!');
    }

Package migration file:

Schema::create('packages', function (Blueprint $table) {
            $table->increments('package_id');
            $table->string('truck_number')->nullable(); 
            $table->foreign('truck_number')->references('truck_number')->on('trucks')->onDelete('cascade');
            $table->string('package_number')->unique();
            $table->string('receiver_name');
            $table->string('destination');
            $table->timestamps();
        });

postman migration file:

Schema::create('postmen', function (Blueprint $table) {
            $table->increments('postman_id');
            $table->string('truck_number')->nullable();
            $table->foreign('truck_number')->references('truck_number')->on('trucks')->onDelete('cascade');
            $table->string('postman_number')->unique();
            $table->string('postman_name');
            $table->timestamps();
        });

truck migration file:

Schema::create('trucks', function (Blueprint $table) {
            $table->increments('truck_id');
            $table->string('truck_number')->unique();
            $table->string('postman_name')->nullable();
            $table->date('date_of_operation');
            $table->timestamps();
        });

Models:

class postman extends Model
{

    use HasFactory;
    protected $primaryKey = 'postman_id';

    protected $fillable = ['truck_number','postman_number','postman_name'];

    public function Truck(){
        return $this->belongsTo(Truck::class);
    }
}

class Package extends Model
{
    use HasFactory;
    protected $primaryKey = 'package_id';

    protected $fillable = ['truck_number', 'package_number', 'receiver_name', 'destination'];
    
     public function Truck(){
        return $this->belongsTo(Truck::class);
    }

}

class Truck extends Model
{
    use HasFactory;
    protected $primaryKey = 'truck_id';
    protected $fillable = ['truck_number', 'postman_name', 'date_of_operation', 'status'];
    
     public function Package()
    {
        return $this->hasMany(Package::class, 'truck_number', 'truck_number');
    }

     public function postman()
    {
        return $this->hasOne(postman::class, 'truck_number', 'truck_number');
    }

}

note: by the way, I also cannot add $table->foreign('postman_name')->references('postman_name')->on('postmen')->onDelete('cascade'); to truck migration file. it throws this error when I do that:

SQLSTATE[HY000]: General error: 1005 Can’t create table proj_db.trucks (errno: 150 “Foreign key constraint is incorrectly formed”)

Answer

You need to change postman_name column to be unique on postmen table:

$table->string('postman_name')->unique();

And in your Truck model you need to specify the foreign key name and the local key name, like this:

return $this->hasOne(postman::class, 'postman_name','postman_name');

Change your relationship in postman Model

public function truck(){
    return $this->belongsTo(Track::class, 'postman_name', 'postman_name');
}

and in Truck model

public function postman(){
    return $this->hasOne(Postman::class, 'postman_name', 'postman_name');
}

Advertisement

Answer