Foreign key constraint fails on nullable field

Question

I have a table organizations. This table has a primary id (int 10, unsigned, AUTO_INCREMENT).

In the table organizations, I also have a foreign key to the iself: main_organization_id. This has the following attributes: (int 10, unsigned, nullable, empty:TRUE, default:NULL).

Whenever I create a new organization:

  $organization = Organization::create($request->all());

Without a main_organization_id in my request, it fails with the following error:

SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails (myDB.organizations, CONSTRAINT organizations_main_organization_id_foreign FOREIGN KEY (main_organization_id) REFERENCES organizations (id)) (SQL: insert into organizations (main_organization_id) values ())

But why does this fail? The field is nullable, does that mean I have to implicitly set main_organization_id to null in the request?

My $fillable:

 protected $fillable = [
        'main_organization_id',
    ];

My migration:

Schema::table('organizations', function (Blueprint $table) {

    $table->integer('main_organization_id')->unsigned()->nullable();
    $table->foreign('main_organization_id')->references('id')->on('organizations');
});**strong text**

I want to prevent code like this: $request['main_organization_id'] = null; before creating my new row. Is this possible?

Thank you for reading.

Can you check what is in your request ($request->all()) from the SQL error it seems that it's either empty or only single field is passed - main_organization_id — michaeltintiuc
If you are send the main_organization_id field through a form you may create a "setter" method on your model to check for empty values and cast them as null — michaeltintiuc

Saumya Rastogi Saumya Rastogi · Accepted Answer · 2016-12-12T13:43:33

Yes, you should specify the field value while creating an Organization, you should do it like this:

$organization = Organization::create(array_merge([
    'main_organization_id' => null,
], request()->all()));

Hope this helps!

Foreign key constraint fails on nullable field

1 Answers