自动填写外键行

Question

I'm trying to create a login/register system for a project using laravel, and since it's my first time, I've been running into a lot of troubles, so I'd ask to please forgive me for any extremely dumb mistakes I've made.

I've already succesfully added a new custom field to the default registration screen, but in my modified users table, I also have two foreign keys referencing other tables ('gameInfo_id' refers to the 'gameInfo' table, and 'role_id' refers to the 'roles' table)

This is the error I'm getting:

Does this mean I have to find a way for the foreign key to be filled in automatically? If so, how would I go about doing this? I've done some googling and found that this usually seems to be issue, but I've never found a clear solution.

Thank you!

Here's my migrations in the users table:

      Schema::create('users', function (Blueprint $table) {
        $table->increments('id');
        $table->string('firstName');
        $table->string('lastName');
        $table->string('email')->unique;
        $table->string('password');
        $table->integer('gameInfo_id')->unsigned();
        $table->integer('role_id')->unsigned();
        $table->timestamps();
    });

    Schema::table('users', function($table) {
        $table->foreign('gameInfo_id')->references('id')->on('gameInfo');
        $table->foreign('role_id')->references('id')->on('roles');
    });

gameInfo is a table of scores that the user would achieve in a game we're also making. What I'm trying to make happen is, when a new user registers an account, it creates a new row in gameInfo, to which the gameInfo_id foreign key would refer. The columns of this newly created row could be set to 0 by default if it helps

Answer 1

If the users table already contains datasets, where some of them don't have existing gameInfo_id or role_id, you can't add an foreign key constraint to the table. The table must be in an valid state, before you add an foreign key constraint.

That means: Adding an new foreign key constraint to an existing field is not the best idea. Doing it anyway, you have to fix the integrity before.

If your still on an development environment, the best way to fix this would be to start all over again with migrate:refresh. If the system runs in production with users, you have to fix all users with invalid or unset gameInfo_id and role_id. If you have just a few users, you could do this by hand. If you have many users, you could do some magic inside the migration script (iterate over all users and add missing references, before you add fk constraints).