What does this SQL error mean?

Question

I am getting the following error when running a migration on Laravel. It seems the error comes up when it reaches the part of the migration that updates the User table.

SQLSTATE[23000]: Integrity constraint violation: 1452
Cannot add or update a child row: a foreign key constraint fails
(`priatek`.`#sql-52e_a`, CONSTRAINT user_usertypeid_foreign` FOREIGN KEY
(userTypeId`) REFERENCES `UserType` (`userTypeId`))
(SQL: alter table `User` add constraint user_usertypeid_foreign foreign key
(`userTypeId`) references UserType` (`userTypeId`))

Migration

public function up()
{
    Schema::create('UserType', function (Blueprint $table) {
        $table->increments('userTypeId');
        $table->string('name');
        $table->string('description')->nullable();
        $table->boolean('viewUser');
        $table->boolean('viewSponsor');
        $table->boolean('viewQuestion');
        $table->boolean('createUser');
        $table->boolean('createSponsor');
        $table->boolean('createQuestion');
    });

    UserType::create([
        'name'           => 'Executive',
        'viewUser'       => 0,
        'viewSponsor'    => 1,
        'viewQuestion'   => 0,
        'createUser'     => 0,
        'createSponsor'  => 0,
        'createQuestion' => 0,
    ]);

    //more UserType creations...

    Schema::table('User', function ($table) {
        $table->integer('userTypeId')->unsigned();
        $table->integer('operatorId')->unsigned();
        $table->integer('sponsorId')->unsigned()->nullable();

        $table->foreign('userTypeId')->references('userTypeId')->on('UserType');
    });

    // more unrelated stuff...
}

You are trying to insert (update) a key that already exist.... — ScaisEdge, Dec 17 '15 at 17:34

score 1 · Accepted Answer · answered Dec 17 '15 at 17:39

1

The problem is when you add the foreign key to the table, all your users must already have a valid userTypeId. But because you are just now creating that new column, they will not have this.

Before you can create the foreign key, you have to make sure all your users have a valid user type (the user type needs to be created and the userTypeId is set appropriately on each user).

So what you need to do is add the additional columns onto the User table, then run some update queries to make sure all users have their userTypeId set, then add the foreign key after the updates are finished.

answered Dec 17 '15 at 17:39

user1669496

32,176
9
73
65

I see what you mean, it's entering 0 for those new foreign keys. I made them nullable since some users might be of type "Admin" and those foreign keys are irrelevant to them. – jstudios Dec 17 '15 at 18:05
Admittedly, I was not aware nullable foreign keys were not only possible, but recommended for situations like these so I'm happy you explained as I did not consider that a solution. – user1669496 Dec 17 '15 at 18:13
I wasn't aware either :/. I'll know in the long run if it works for this project. – jstudios Dec 17 '15 at 18:24

What does this SQL error mean?

1 Answers1