Integrity constraint violation Obj::createFromArray($array)

[ghost]

When you run

$array = [
            [
                'id' => 90,
                'name' => 'About',
                'position' => 0,
                'children' => [
                    [
                        'id' => 93,
                        'name' => 'Testimonials'
                    ]
                ]
            ],
            [
                'id' => 91,
                'name' => 'Blog',
                'position' => 1
            ],
            [
                'id' => 92,
                'name' => 'Portfolio',
                'position' => 2
            ],
        ];

        $pages = Page::createFromArray($array);

You get the error:

[Illuminate\Database\QueryException]                                         
  SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update   
  a child row: a foreign key constraint fails (`DATABASE`.`TABLE`, C  
  ONSTRAINT `TABLE_parent_id_foreign` FOREIGN KEY (`parent_id`) REFERENC  
  ES `TABLE` (`id`)) (SQL: insert into `TABLE` (`position`, `real_d  
  epth`) values (0, 0))    

I generated the database using:

php artisan closuretable:make --entity=page

[franzose]

Update your package, please. I had an email from you sent by Github where the message was about missing deleted_at column in the entity table. Now, here, I see the message is about foreign key but this issue was already solved: there was a mistake in the stub and I have fixed already.

Notice that you need to migrate your tables again to make them properly set.

[ghost]

The package has been updated, and I have migrated the tables again. The other issues were fixed by the package update but this is an additional one. The other foreign key issue occurred when migrating the database but this issue occurs when you are seeding the database. But just incase I had originally messed something up I just checked again to make sure, and the issue persists.

[franzose]

Did you try to createFromArray without providing id? However, I will also now test the method.

[ghost]

I just tested it and I got the same result. I also tried removing position, but the same error popped up.

[tomzx]

Hi @kmccarthyweb,

Do you still experience the same issue as mentioned in your original post? From the error displayed in the query, it seems that neither the id nor the name fields are being saved (only the position = 0 and real_depth = 0). This seems like a buggy behavior to me.

Second, do you get any errors if you run the test suite of this package? That is, does EntityTestCase::testCreateFromArray give you an error?

Out of curiosity, what database software are you using? MySQL, PostgreSQL, etc.?

[ghost]

I am still having the issue. I am using MySQL.

I added the $fillable key to the table that was generated after migrating the database. The categories table looks like this:

id | parent_id | position | real_depth | deleted_at | name

When I try to seed the database I get the following error:

[Illuminate\Database\QueryException]                                         
  SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update   
  a child row: a foreign key constraint fails (`DATABASE`.`categories`, C  
  ONSTRAINT `categories_parent_id_foreign` FOREIGN KEY (`parent_id`) REFERENC  
  ES `categories` (`id`)) (SQL: insert into `categories` (`id`, `name`, `posi  
  tion`, `real_depth`) values (90, About, 0, 0))                               

[tomzx]

Hi @kmccarthyweb,

Could you please generate the SQL code that would generate the tables? Or provide us with the migration files you are running? It would seem that your parent_id column has NOT NULL instead of DEFAULT NULL.

`parent_id` int(10) unsigned NOT NULL,

should be

`parent_id` int(10) unsigned DEFAULT NULL,

[dominiczaq]

@kmccarthyweb, maybe you haven't run composer update before creating the migration. As I said here #75 I had the same problem - the migration with parent_id column was not accepting nulls.

[ghost]

@tomzx After looking at the database in SequelPro it looks like you're right. parent_id doesn't allow null and has no default. Here are my migrations:

<?php

use Illuminate\Database\Schema\Blueprint;
use Illuminate\Database\Migrations\Migration;

class CreateCategoriesTable extends Migration {

    public function up()
    {
        Schema::create('categories', function(Blueprint $table){
            $table->increments('id');
            $table->integer('parent_id')->unsigned();
            $table->integer('position', false, true);
            $table->integer('real_depth', false, true);
            $table->softDeletes();

            $table->foreign('parent_id')->references('id')->on('categories');
        });
    }

    public function down()
    {
        Schema::drop('categories');
    }
}

<?php

use Illuminate\Database\Schema\Blueprint;
use Illuminate\Database\Migrations\Migration;

class CreateCategoryClosuresTable extends Migration {

    public function up()
    {
        Schema::create('category_closure', function(Blueprint $table){
            $table->increments('ctid');

            $table->integer('ancestor', false, true);
            $table->integer('descendant', false, true);
            $table->integer('depth', false, true);

            $table->foreign('ancestor')->references('id')->on('categories');
            $table->foreign('descendant')->references('id')->on('categories');
        });
    }

    public function down()
    {
        Schema::drop('category_closure');
    }
}

@dominiczaq I ran composer update again to be sure but everything is up to date.

[dominiczaq]

@kmccarthyweb, please run the php artisan closuretable:make again -> composer has updated your migration generator (you can check it in your files \vendor\franzose\closure-table\src\Franzose\ClosureTable\Generators\stubs\migrations), but not migration file you have already created. Or you can add nullable() to your column by hand, with the same result :)

[ghost]

@dominiczaq You're right. I hadn't noticed it didn't run the migration again. My mistake. Everything works as it should now! Thanks!