What does this SQL error mean?
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...
}
Answer
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.
Related Questions
- → I can't do a foreign key, constraint error
- → How to implement DbDongle::convertTimestamps as workaround of invalid timestamps with MySql strict
- → MySQL error "Foreign key constraint is incorrectly formed"
- → Eloquent Multitable query
- → "Laravel 5.1" add user and project with userId
- → Database backup with custom code in laravel 5 and get the data upto 10 rows from per table in database
- → Laravel 5.1 QueryException when trying to delete a project
- → Using Array in '->where()' for Laravel Query Building
- → Chaining "Count of Columns" of a Method to Single Query Builder
- → Laravel Eloquent Joining Strange query
- → convert time using mysql laravel 5
- → How to update a column after an expiration date in MySQL?
- → Foreign key constraint fails on existing key