Laravel: Cannot add Foreign key Constraint

Asked

Viewed 1,661 times

4

I have two tables, the table users and empresas, with Model User and Empresa respectively. A user registration can manage a company, and for that, I need to define which id the user will access in the table of companies.

I’m looking to create a column in the table users associated with a table row empresas. However, this column may be null.

That’s the Migration I’m trying to run to associate the column empresa_id inside users for the primary key in empresas:

public function up()
{
    Schema::table('users', function($table) {
        $table->integer('empresa_id')->unsigned()->nullable();
        $table->foreign('empresa_id')->references('id')->on('empresas');
    });
}

Column definition empresas.id:

Schema::create('empresas', function (Blueprint $table) {
        $table->bigIncrements('id');

The Migration of the creation of Foreign key is the last Migration to be executed with the command php artisan migrate:fresh. Prior to this, previous Migrations (including those that create tables users and empresas) run smoothly.

And after executing the command, I get the following error:

Illuminate Database Queryexception : SQLSTATE[HY000]: General error: 1215 Cannot add Foreign key Constraint (SQL: alter table users add Constraint users_empresa_id_foreign Foreign key (empresa_id) References empresas (id))

The table empresas and users are already created.

What am I doing wrong?

php sql laravel laravel-eloquent

  • The migrate:fresh drop all tables, then are not "created", so if you are trying to add missing relationship Scheme::create('empresas', ...) and Scheme::create('users', ...) in the methods up(), I don’t know much about it, so I’m just saying.

  • @Guilhermenascimento drops everything and then runs all Migrations again. Before this Migration, there are already Migrations that create both tables.

  • Ok, run the Migrations in order? Do you or do you not have the creates in the ups? In your Migration I only see the up to generate the relation, I do not see "the tables", it is difficult to say what is missing.

  • Maybe it is the order that the Migrations execute, I had a similar problem once, I changed the names of the Migrations (the date at the beginning) to reorganize the execution orders and started to work

  • I don’t know much about Migrations, I use the basics of the Laravel and the rest I end up doing in the hand, this when I use Aravel, because in practice I find a huge FW exaggerated and poorly performatico, but I’m not going to criticize, I go straight to the point, as far as I think a Migration can represent more than one table, people are the one who has a habit of creating a table for each Migration and expect Artisan to understand what the person did, so the method up() would be the answer to create what is needed, but I may be talking nonsense, in fact I only used Migration a few times.

  • I updated the question with more details regarding the Migrations order.

  • 4

    bigIncrements creates a column like BigInteger; you created a FK of the type Integer. There is no way to make a FK reference a PK of different types.

  • What happens is this, every time you turn the php artisan migrate, it puts in the queue, everything that needs to be processed, even what has already been processed, every time you create a new modification, you have to put in the down method: public function down() {...} what will be dropped... otherwise it will give error when it already exists...

  • Possible duplicate of General error: 1215 Cannot add Foreign key Constraint

  • @Ivanferrer is not dup, the question is more to typo, see Woss' comment and his answer the problem is ANOTHER, the error triggered is the same, but only because it is a Generic error that says it was not possible to relate X to Y.

  • @Guilhermenascimento, so it is a possible duplicate, perhaps the solution presented to the error in this other answer solves the same problem of it.

  • @Ivanferrer is not even possible, the error is Generico, was similar to once another user commented that it was dup an HTTP error 500 for one question based on another, but error 500 is as generic as possible, questions so are rarely dups, because they are isolated errors and the error message is only Generica, only analyzing part of the error of the AP to know what happened, and the response and comment of Woss that was made 20 minutes before already makes it all clear.

  • Then it would be the case to close because it is not clear enough, since it is generic, there is not enough information for a conclusive answer. But apparently, @Woss has already answered the problem.

  • @Ivanferrer yes I agree that it lacked details, but within the few that had the WOSS detected the error of the AP, soon typing error, because it was on the part of the AP, not that the question has to be deleted, rarely it can even help someone, but it is an "odd" error even. ;)

  • I don’t even think it’s a typo, it’s a logic error in the use of the framework, for me it stays open even if.

  • @Erloncharles as can be logic error in FW if bigIncrements indicates a field type (within FW) and integer indicates another type, so you say that A is type X and must communicate with B (or through it) with type Y is the same as waiting for your outlet to be easily connected on "European switches" (incidentally it would be easier to connect the same outlet than to make a FW understand a human error)

  • What you have just described is socket interface logic... Typo would be writing bigIncements or interger, or anything like that, what happened was a failure to implement FK in Migration.

  • 1

    If there are more discussions I think we should go to a chat! comments are getting excessive.

Show 13 more comments

2 answers

7


On seeing the documentation, you will see:

$table->bigIncrements('id')

Incrementing ID using a "big integer" equivalent.

That is, a "big integer" column will be created as the primary key.

In migration you have defined a foreign key as "integer":

$table->integer('empresa_id')

To make the relation, the foreign primary and key types must be the same. Can’t a FK reference a PK of another type (even if in this case all possible values in FK would be valid in PK).

To fix, just set the columns with the same type:

  1. Changing the FK to $table->bigInteger('empresa_id'); or
  2. Changing the PK to $table->increments('id');

-2

To create a foreign key that accepts the null value you must add nullable() before constrained(), as below:

Schema::create('rooms', function (Blueprint $table) {
            $table->id();
            $table->foreignId('employ_id')->constrained('employs');
            $table->foreignId('studant_id')->nullable()->constrained('studants');           
            $table->timestamps();
        });

Browser other questions tagged php sql laravel laravel-eloquent

You are not signed in. Login or sign up in order to post.