Integrity constraint violation: 1452 Cannot add or update a child row

[julianovmartins]

Describe the bug The JobTranslation model is attempting to insert the primary key id of the Job model instead of inserting the value of the base_id field that was referenced as a custom foreign key.

To Reproduce

CreateJobsTable Migration:

class CreateJobsTable extends Migration
{
    public function up()
    {
        Schema::create('jobs', function (Blueprint $table) {
            $table->bigIncrements('id');
            $table->integer('base_id')->unique();
            $table->string('type',10);
            $table->string('gender',10);
            $table->tinyInteger('active')->default(0);
            $table->timestamps();
        });
    }

    public function down()
    {
        Schema::dropIfExists('jobs');
    }
}

CreateJobTranslationsTable Migration:

class CreateJobTranslationsTable extends Migration
{
    public function up()
    {
        Schema::create('job_translations', function (Blueprint $table) {
            $table->bigIncrements('id');
            $table->integer('job_base_id');
            $table->string('locale', 20)->index();
            $table->string('name', 50);
            $table->timestamps();
            $table->unique(['job_base_id', 'locale']);
            $table->foreign('job_base_id')->references('base_id')->on('jobs')->onDelete('cascade');
        });
    }

    public function down()
    {
        Schema::dropIfExists('job_translations');
    }
}

Job Model:

class Job extends Model
{
    use Translatable;

    public $translationModel = JobTranslation::class;

    public $translationForeignKey = 'job_base_id';

    protected $table = 'jobs';

    protected $primaryKey = 'id';

    public $translatedAttributes = ['name'];

    public $fillable = ['base_id', 'type', 'gender', 'active'];
}

JobTranslation Model:

class JobTranslation extends Model
{
    protected $table = 'job_translations';

    public $timestamps = true;

    protected $primaryKey = 'id';

    protected $fillable = ['job_base_id', 'locale', 'name'];

    public function job(): BelongsTo
    {
        return $this->belongsTo(Job::class, 'job_base_id', 'base_id');
    }
}

JobSeeder:

class JobSeeder extends Seeder
{
    public function run()
    {
        $item = [
            'base_id' => 5,
            'type' => 'normal',
            'gender' => 'male',
            'active' => 1,
            'en' => [
                'name' => 'Test name'
            ],
        ];

        Job::create($item);
    }
}

Expected behavior

insert into `jobs` (`base_id`, `type`, `gender`, `active` , `updated_at`, `created_at`) values (5,'normal','male', 1, '2021-04-23 12:37:07', '2021-04-23 12:37:07');

The last id is 1, and the base_id is 5.

The insertion below is correct and should work that way.

insert into `job_translations` (`locale`, `name`, `job_base_id`, `updated_at`, `created_at`) values ('en', 'Test name', 5, '2021-04-23 12:37:07', '2021-04-23 12:37:07');

Versions (please complete the following information)

• PHP: 7.4.16
• Database: MySQL 8.0.26
• Laravel: 8

Exception

The values should be: (en, Test name, 5, ..., ...)

But that insists on adding the last inserted Job id.

 SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails (`job_translations`, CONSTRAINT `job_translations_job_base_id_foreign` FOREIGN KEY (`job_base_id`) REFERENCES `jobs` (`base_id`) ON DELETE CASCADE) (SQL: insert into `job_translations` (`locale`, `name`, `job_base_id`, `updated_at`, `created_at`) values (en, Test name, 1, 2021-04-23 12:37:07, 2021-04-23 12:37:07))

[Gummibeer]

In case you want to use base_id as the primary key you should define it on the Job model. But the described behavior is correct and expected - we use the primary key of the main model to put it in the foreign key column of the translation model.

[julianovmartins]

I changed the name of the field "base_id" to "id" and removed the auto-increment, as they are pre-defined IDs.

The last job id entered is 2.

 SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '1-en' for key 'job_translations.job_translations_job_id_locale_unique' (SQL: insert into `job_translations` (`locale`, `name`, `job_id`, `updated_at`, `created_at`) values (en, Test name 2, 1, 2021-04-23 20:42:47, 2021-04-23 20:42:47))

Job Migration:

Schema::create('jobs', function (Blueprint $table) {
    $table->unsignedBigInteger('id')->primary();
    $table->string('type', 10);
    $table->string('gender', 10);
    $table->tinyInteger('active')->default(0);
    $table->timestamps();
});

JobTranslation Migration

Schema::create('job_translations', function (Blueprint $table) {
    $table->bigIncrements('id');
    $table->unsignedBigInteger('job_id');
    $table->string('locale', 20)->index();
    $table->string('name', 50)->nullable(true);
    $table->timestamps();
    $table->unique(['job_id', 'locale']);
    $table->foreign('job_id')->references('id')->on('jobs')->onDelete('cascade');
});

Job Model:

class Job extends Model
{
    use Translatable;
    public $translationModel = JobTranslation::class;
    protected $table = 'jobs';
    protected $primaryKey = 'id';
    public $translatedAttributes = ['name'];
    public $fillable = ['id', 'type', 'gender', 'active'];
}

So, I commented on the unique constraint to do a test:

// $table->unique(['job_id', 'locale']);

And got it:

This keeps ID 1 in memory for entering in the "job_id" field.

[Gummibeer]

The unique constrained has to be there as a model should have only one translation record per locale. Having multiple translations for the same locale and model isn't useful.

You should also tell the main job model that it doesn't have an incrementing primary key anymore.

[julianovmartins]

You're right! I just needed to inform Model that I don't need auto increment.

public $incrementing = false;

It works fine now!

Thank you!