How to keep foreign key restriction using softdelete?

Question

How can I maintain the integrity of my database when using softdelete?

Example: try to exclude a person which is linked to a account through a foreign key, in normal ways this will not be possible because it violates the restriction of foreign key, but using the softdelete is possible even because softdelete is a update.

Someone has a tip on how to keep foreign key restriction using softdelete?

Observing: I’m using the method

delete();

Answer 1

You’re quite right in what you say, but this is one of the ways proposed by eloquent not to summarily exclude the data (permanently), standing up as historical in its groundwork, with the possibility of searching for old data, which is very important in my view.

I believe the demonstration of a minimal example of a relationship would be ideal for your understanding:

Models

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');
    }
}

Bill

<?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 a command is executed delete() in the model Pessoa, as an example below:

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

how is using the resource Softdeletes, the field is updated deleted_at with a current date and with that record is considered deletado. What I could understand is that you want to simulate what the bank would do by giving a erro because there are data in the relationship with the model Conta, that is, exists in the table of accounts data with the relationship of person of id equal 1. A similar mistake as this:

17:42:03 delete from people Where id = 1 Error Code: 1451. Cannot 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 record by using Softdeletes?

The only way is to do research in existing relationships with pessoas, in the example contas:

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

this is the way it has, ie, via programming, but, there may be a problem, if the table pessoas has various relations have to check all relations, to then give the command delete(). It is worth remembering that in contas may also be using the Softdeletes, and if by chance all accounts are automatically deleted, you can exclude the pessoa with this simple code. If you prefer you can give a message that this person cannot be excluded by having relations (pendencia):

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";
}

Observing: if all your models (Model) are using the Softdeletes, is ideal to use the command delete in relations (there is nothing implemented that resolves in a single way, at least until now), and only remembering that with this resource its base would be with the information history and with extra commands, this data can be recovered.

Example of how to recover a deleted record with Softdeletes

Recovering the Deleted Record:

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

Restoring the deleted item:

$pessoa->restore();

And finally, there is also a form of force the summary exclusion (really delete the database record) with the following method:

Permanently delete the record from the table:

$pessoa->forceDelete();

_{Observing: if there is a relation between the tables and records that makes the relation, at the time of exclusion the database will not allow and will send an error Foreign key Constraint}

References:

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

Answer 2

I will summarize the solution I adopted in a few steps:

1. Trying to force physical delete (definitive), as there is foreign key restriction in the database the method executes a rollback in the transaction.

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

2. Runs a logical delete (softdelete).

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

3. Confirms the transaction.

DB::commit()

4. Retrieving the 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