ssquare's avatar

Unable to alter foreign column from nullable to not null in Laravel migration

I want to alter the nullable column to not nullable. Using: laravel 5.8

I have a table with unit_id, building_id and property_id as nullable. Now, I want to update it to not null. However, the migration is returning following error:

 SQLSTATE[HY000]: General error: 1832 Cannot change column 'building_id': used in a foreign key constraint 'amenity_pricing_reviews_building_id_foreign' (SQL: ALTER TABLE
 amenity_pricing_reviews CHANGE unit_id unit_id INT UNSIGNED NOT NULL, CHANGE building_id building_id INT UNSIGNED NOT NULL, CHANGE property_id property_id INT UNSIGNED NOT NULL, CHANGE dom dom VARCHAR(191) CHA
RACTER SET utf8mb4 DEFAULT NULL COLLATE `utf8mb4_unicode_ci`)

And my schema looks like:

    public function up()
    {
        Schema::table('amenity_pricing_reviews', function (Blueprint $table) {

            $table->dropForeign(['unit_id','building_id','property_id']);
            $table->unsignedInteger('unit_id')->nullable(false)->change();
            $table->unsignedInteger('building_id')->nullable(false)->change();
            $table->unsignedInteger('property_id')->nullable(false)->change();
            $table->foreign('unit_id')
                ->references('id')
                ->on('units');
            $table->foreign('building_id')
                ->references('id')
                ->on('buildings')
                ->onDelete('cascade');
            $table->foreign('property_id')
                ->references('id')
                ->on('properties')
                ->onDelete('cascade');
            $table->string('dom')->nullable()->change();
        });
    }

This is how I had created these columns on first time:

$table->unsignedInteger('unit_id')->nullable();
$table->foreign('unit_id')->references('id')->on('units')->onDelete('cascade');
$table->unsignedInteger('building_id')->nullable();
$table->foreign('building_id')->references('id')->on('buildings')->onDelete('cascade');
$table->unsignedInteger('property_id')->nullable();
$table->foreign('property_id')->references('id')->on('properties')->onDelete('cascade');
            
0 likes
2 replies
vincent15000's avatar

Hello @ssquare I had the same problem and the only solution I have found is to refresh the table, it seems to be not possible to update from nullable to not nullable because of constraints : if you have a null column, the you want to update it to not null, it is impossible because there is no value in it.

ssquare's avatar

@vincent15000 I had already deleted all the rows which have null value as:

       AmenityPricingReview::whereNull('unit_id')
                            ->orWhereNull('building_id')
                            ->orWhereNull('property_id')
                            ->withTrashed()
                            ->forceDelete();

In fact, I had tried by dropping all datas from amenity_pricing_reviews as well, but with no success.

Please or to participate in this conversation.