How to keep the foreign key constraint using softdelete?

You are very right in what it says, but this is one of the forms proposed by > eloquent to not summarily delete the data ( permanently ), even getting as history in your base , having the possibility of old data searches, which is very important in my understanding.

I believe that demonstrating a minimum example of a relationship would be ideal for your understanding:

Templates

Person:

<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Model;
use Illuminate\Database\Eloquent\SoftDeletes;

class Pessoa extends Model
{
    use SoftDeletes;

    protected $primaryKey = "id";
    protected $fillable = array('nome');
    protected $table = "pessoas";
    public $timestamps = false;
    protected $dates = ['deleted_at'];

    public function contas()
    {
        return $this->hasMany(Conta::class, 'pessoa_id','id');
    }
}

Account

<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Model;
use Illuminate\Database\Eloquent\SoftDeletes;

class Conta extends Model
{
    use SoftDeletes;

    protected $primaryKey = "id";
    protected $fillable = array('conta', 'pessoa_id');
    protected $table = "contas";
    public $timestamps = false;
    protected $dates = ['deleted_at'];

    public function pessoa()
    {
        return $this->belongsTo(Pessoa::class,'pessoa_id','id');
    }
}

If you run a command delete() in model Pessoa , as an example below:

Pessoa::find(1)->delete();

Since you are using the SoftDeletes feature, the deleted_at field with a current date is updated and therefore, this record is considered deletado . What I could understand is that you want to simulate what the bank would do, giving erro because there is data in relation to the model Conta , that is, it exists in the > accounts with the person relationship of id equal 1 . A similar error like this:

  

17:42:03 delete from people where id = 1 Error Code: 1451. Can not   delete or update a parent row: a foreign key constraint fails   ( db1 . contas , CONSTRAINT contas_pessoa_id_foreign FOREIGN KEY   ( pessoa_id ) REFERENCES pessoas ( id )

How could you then protect this registry by using SoftDeletes SoftDeletes

The only way is to search for existing relationships with pessoas , in this example contas :

if (Conta::where('pessoa_id', 1)->count() == 0)
{
    Pessoa::find(1)->delete();
}

This is the way it has, ie via programming , but it has perhaps a problem if the% If you have multiple relations you have to check all relations, and then give the command pessoas . Note that in delete() you may also be using the SoftDeletes feature, and if you happen to be excluded all accounts can automatically exclude contas with this simple code. If so you can give a message that this person can not be deleted, for having relationships (pending):

if (Conta::where('pessoa_id', 1)->count() == 0)
{
    Pessoa::find(1)->delete();
} 
else
{
    //texto meramente ilustrativo, pode ser tomada outras decisões
    echo "Não pode ser excluido por ter relação com contas";
}

Note: If all of your templates ( pessoa ) are using the SoftDeletes, it is ideal to use the Model command in the relations (there is nothing implemented that solves it in a way, at least until now), and only remembering that with this feature your base would have the < Information history and with extra commands, this data can be retrieved.

Example of how to recover a deleted record with SoftDeletes

Retrieving the deleted record :

$pessoa = Pessoa::withTrashed()
            ->where('id', 1)
            ->first();

Restoring the deleted item :

$pessoa->restore();

And lastly, there is also a summary form of force (delete actually the bank record) with the following method:

Permanently delete the table record :

$pessoa->forceDelete();

Note: If there is a relationship between the tables and records that make the relation, at the time of deletion the bank would not allow and send an error foreign key constraint

References:

• Soft Deleting
• Defining Models
• Querying Soft Deleted Models
• SQL FOREIGN KEY Constraint

I'll summarize the solution I took in a few steps:

$pessoa->forceDelete();
DB::rollback();

2. Performs a logicaldelete (softdelete).

$pessoa = Pessoa::findOrFail($id);
$pessoa->delete();

3. Confirm the transaction.

DB::commit()

4. Retrieving Exceptions: QueryException (PDOException),

catch (QueryException $e) {
    DB::rollback();
    $pessoa = array('mensagem' => 'impossível excluir esse dado');

Below the full code, I hope it's clear.

    use Illuminate\Database\QueryException;

    private function destroy($id)
    {

    DB::beginTransaction();

    try {

        $pessoa = Pessoa::findOrFail($id);

        //1
        $pessoa->forceDelete();
        DB::rollback();

        //2
        $pessoa = Pessoa::findOrFail($id);
        $pessoa->delete();

        //3
        DB::commit();

        //4
    } catch (QueryException $e) {
        DB::rollback();
        $pessoa = array('mensagem' => 'impossível excluir esse dado');
    }

        return $pessoa;
    }

Reference: Database Transactions