Laravel - Cannot add foreign key constraint

Question

I am trying to add FK 'module_id' to my table 'documents'. I have ran the following query:

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

            $table->integer('module_id')->unsigned();
            $table->foreign('module_id')->references('id')->on('modules');

        });
    }

The following error is being returned:

SQLSTATE[HY000]: General error: 1215 Cannot add foreign key constraint (SQL: alter table documents add constraint documents_module_id_foreign foreign key (module_id) references modules (id))

I'm not sure what I'm doing wrong, I'm sure it is probably a silly mistake but I have spent a lot of time going around in circles trying to figure it out... here is what I have tried..

both tables are already created
the data types for both column's are consistent (both unsignedBigInts, 20)

I have included a picture of my DB tables, i appreciate any help.

Update:

SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails (laravel.#sql-2cd_23, CONSTRAINT documents_module_id_foreign FOREIGN KEY (module_id) REFERENCES modules (id)) (SQL: alter table documents add constraint documents_module_id_foreign foreign key (module_id) references modules (id))

mrhn · Accepted Answer · 2020-04-01 11:40:33Z

1

The type of the column needs to be a big integer.

    Schema::table('documents', function (Blueprint $table) {
        $table->unsignedBigInteger('module_id');
        $table->foreign('module_id')->references('id')->on('modules');

    });

Update

You probably already got data in your tables, since the column can't be null the foreign key can't exists. Starting it out as nullable then adding the relationships and removing the nullable would fix it. So:

$table->unsignedBigInteger('module_id')->nullable();

edited Apr 1, 2020 at 11:40

answered Apr 1, 2020 at 11:07

mrhn

19.1k6 gold badges31 silver badges48 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Zak Over a year ago

Hi, thanks for this.. i made the changes but produced an error (I've attached in my original qs... could you advise?

Zak Over a year ago

Thank you! I deleted the data (wasn't relatively important) and it worked. Appreciate it

Collectives™ on Stack Overflow

Laravel - Cannot add foreign key constraint

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related