Integrity Constraint Violation in "Many to Many" create - Laravel

Question

Context: When registering a company, by being "attached" several customers to it and that same customer can be "attached" in other companies.

I have the following database structure:

Model "Client":

public function enterprises()
{
    return $this->belongsToMany('App\Models\Enterprise', 'enterprise_client');
}

Model "Enterprise":

public function clients()
{
    return $this->belongsToMany('App\Models\Client', 'enterprise_client');
}

Pivot "enterprise_client":

public function up()
{
    Schema::create('enterprise_client', function (Blueprint $table) {
        $table->increments('id');
        $table->integer('enterprise_id')->unsigned();
        $table->foreign('enterprise_id')->references('id')->on('enterprises')->onDelete('restrict');
        $table->integer('client_id')->unsigned();
        $table->foreign('client_id')->references('id')->on('clients')->onDelete('restrict');
        $table->timestamps();
    });
}

To save the data:

if (isset($dataRequest['clients'])) {
            $enterprise->clients()->sync($dataRequest['clients']);
        }

"$dataRequest['clients']" is an array of ID’s. " isset()" is to check if you have this array in Request, because during registration, client completion is not required.

However, when trying to save the data, I returned the error:

local.ERROR: PDOException: SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails (`my_project`.`enterprise_client`, CONSTRAINT `enterprise_client_client_id_foreign` FOREIGN KEY (`client_id`) REFERENCES `clients` (`id`)) in /home/Workspace/MyProject/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOStatement.php:142

Using attach() instead of sync() returns the same error.

What could be causing this?

Answer 1

From what I understand of your problem, are concerned only two models (Entreprice and Client), in these cases use the hasMany method, in the sense that a company can have several customers, as well as a customer can be in several companies. It is a direct relationship between two tables.

The method belongsToMany() is used for relationships with more tables, when you have for example, a third Pivot table that makes relationships between customers and companies.

Also, I think the statements do not have the correct parameters. You should put the ids that make the links between the models:

public function enterprises()
{
    return $this->hasMany('App\Models\Enterprise', 'enterprise_id');
}

public function clients()
{
    return $this->hasMany('App\Models\Client', 'client_id');
}

EDIT: I didn’t really notice the pivot table in the comments, my fault. I simulated all models and managed to insert the records in the pivot table without problems. I used this code to test:

$enterprise = Enterprise::first();

$dataRequest['clients'][0] = '1';
$dataRequest['clients'][1] = '2';
$dataRequest['clients'][2] = '3';

if (isset($dataRequest['clients'])) {
 $enterprise->clients()->sync($dataRequest['clients']);
}

I had the same error as you when I tried to enter the id of a client that does not exist. For example $dataRequest['clients'][2] = '10';

You can add a Try / Catch to get around this problem, the existing ids are still inserted.

 $enterprise = Enterprise::first();

  $dataRequest['clients'][0] = '1';
  $dataRequest['clients'][1] = '2';
  $dataRequest['clients'][2] = '10';

  if (isset($dataRequest['clients'])) {

        try {
          $enterprise->clients()->sync($dataRequest['clients']);
        } catch (\Exception $e) {
      }
  }