Eloquent Uuids as Foreign keys

Question

I'm trying to use Uuids as foreign keys in my database. I want the Admin table to reference the User table.

The database is PostgreSQL, but it should work anywway.

The Users' table:

    Schema::create('users', function (Blueprint $table) {
        $table->uuid('id')->primary();
        $table->string('name', 80);
        $table->string('email', 80)->unique();

        ...

    });

My Admins' table:

    Schema::create('administrators', function (Blueprint $table) {
        $table->increments('id');
        $table->uuid('fk_user_id');
        $table->foreign('fk_user_id')->references('id')->on('users')->onDelete('cascade');

        ...

    });

I also tried to assign the foreign key as String, since my Uuid trait looks like this:

<?php

namespace App\Models\Concerns;

use Illuminate\Support\Str;

trait UsesUuid
{
    protected static function bootUsesUuid()
    {
        static::creating(function ($model) {
            if (! $model->getKey()) {
                $model->{$model->getKeyName()} = (string) Str::uuid();
            }
        });
    }

    public function getIncrementing()
    {
        return false;
    }

    public function getKeyType()
    {
        return 'string';
    }
}

So my Admins' table looked like this:

    Schema::create('administrators', function (Blueprint $table) {
        $table->increments('id');
        $table->string('fk_user_id', 36);
        $table->foreign('fk_user_id')->references('id')->on('users')->onDelete('cascade');

        ...

    });

I always get the same errors:

[2020-10-15 10:52:44] local.ERROR: SQLSTATE[42804]: Datatype mismatch: 7 ERROR:  foreign key constraint "administrators_fk_user_id_foreign" cannot be implemented
DETAIL:  Key columns "fk_user_id" and "id" are of incompatible types: character varying and uuid. (SQL: alter table "administrators" add constraint "administrators_fk_user_id_foreign" foreign key ("fk_user_id") references "users" ("id") on delete cascade) {"exception":"[object] (Illuminate\\Database\\QueryException(code: 42804): SQLSTATE[42804]: Datatype mismatch: 7 ERROR:  foreign key constraint \"administrators_fk_user_id_foreign\" cannot be implemented
DETAIL:  Key columns \"fk_user_id\" and \"id\" are of incompatible types: character varying and uuid. (SQL: alter table \"administrators\" add constraint \"administrators_fk_user_id_foreign\" foreign key (\"fk_user_id\") references \"users\" (\"id\") on delete cascade) at C:\\MyFolder\\Dev\\MyProjects\\Laravel\\VitruvianShield\\vitruvianshield\\vendor\\laravel\\framework\\src\\Illuminate\\Database\\Connection.php:669)
[stacktrace]

and

[previous exception] [object] (PDOException(code: 42804): SQLSTATE[42804]: Datatype mismatch: 7 ERROR:  foreign key constraint \"administrators_fk_user_id_foreign\" cannot be implemented
DETAIL:  Key columns \"fk_user_id\" and \"id\" are of incompatible types: character varying and uuid. at C:\\MyFolder\\Dev\\MyProjects\\Laravel\\VitruvianShield\\vitruvianshield\\vendor\\laravel\\framework\\src\\Illuminate\\Database\\Connection.php:463)
[stacktrace]

How can I do this? The closest post I found about this was:

laravel 7 using as uuid foreign key

But somehow it doesn't work.

I'm using Laravel 8, btw.

Thank you for your help.

I used [`goldspecdigital/laravel-eloquent-uuid`](https://github.com/goldspecdigital/laravel-eloquent-uuid) package but with `mysql` and with no issues whatsoever. I not only used for schema generation but also for seeders. — Dark Knight, Oct 15 '20 at 11:37
The package works perfectly @JitendraYadav . Thanks! SHould have used it from the beginning. — Luís Henriques, Oct 15 '20 at 12:26
just to make sure, check what is datatype for `uuid` in postgres. Both `string(36)` and `uuid` should have exact datatype — Dark Knight, Oct 15 '20 at 13:20
The error is saying that one column is a uuid type and the other one is a varchar type. Different types can't reference each other — apokryfos, Oct 15 '20 at 13:36
@apokryfo I know. If you read my full question, you'll see I'm well aware of that. But thanks anyway. — Luís Henriques, Oct 15 '20 at 13:42
@JitendraYadav I already checked ;) It's perfect. Thank you once again. — Luís Henriques, Oct 15 '20 at 13:42
Actually it's not clear in your question that you are aware of that. You don't mention trying to change `$table->string('fk_user_id', 36)` to `$table->uuid('fk_user_id')` and what happened after that (i.e. what didn't work what sort of error there was). Yes using a library might have solved the issue for you but it may not be an option to other who have the same issue and find this page in hope for solutions — apokryfos, Oct 15 '20 at 13:47

score 0 · Answer 1 · answered Oct 15 '20 at 13:26

0

try this here:

Schema::create('administrators', function (Blueprint $table) {
        ...

        $table->string('fk_user_id');

        ...

    });
```

answered Oct 15 '20 at 13:26

Martin

316
1
5

Thanks for the reply @Martin. I did try that. With and without the 36 char limit. My problem was already solved by Jitendra Yadav, though. Thanks anyway. – Luís Henriques Oct 15 '20 at 13:38

Eloquent Uuids as Foreign keys

1 Answers1