I am trying to use UUID for my next project and have added a column id, which is a UUID type primary key.
I have another field in table that makes an internal reference ( foreign key ) to the id field. When I try to migrate the migration file it gives an obvious error because ( I guess ) mySQL does not allow you to create foreign key constraint on CHAR type column.
Is there any workaround or do I need to use another Auto-increment/ Integer type column.
Code for my migration file is:
$table->uuid('id');
$table->string('name');
$table->string('email')->unique();
$table->string('password');
$table->softDeletes();
$table->uuid('deleted_by_id')->nullable();
$table->foreign('deleted_by_id')->references('id')->on('users');
$table->rememberToken();
$table->timestamps();
$table->primary('id');
Error that I get:
Illuminate\Database\QueryException : SQLSTATE[HY000]: General error: 1005 Can't create table `laravel_026db`.`#sql-5ec_16d` (errno: 150 "Foreign key constraint is incorrectly formed") (SQL: alter table `users` add constraint `users_deleted_by_id_foreign` foreign key (`deleted_by_id`) references `users` (`id`))
MySQL cannot add the foreign key to itsef im the same schema. What I mean by that is that MySQL is trying to reference users table while creating the users table. I believe that could be solved by newer MySQL version. Just add another migration that adds a foreign key.
Use ID as auto-incremental integer and reference that throughout your databae and app as an internal key for performance, but use UUID as “public” key that is shown to the users. My 0.02.
@crnkovic I don't think that is the case.
I had the same migration file earlier in Laravel 5.3, only difference being the UUID, earlier the id field was big integer and now the UUID.
deleted_by_id is nullable while id is not, maybe that is the case. Dump the SQL that builds the tables and paste here.
I had deleted_by_id as nullable in previous migration and it was working.
Here is a error stack:
Illuminate\Database\QueryException : SQLSTATE[HY000]: General error: 1005 Can't create table `laravel_026db`.`#sql-5ec_16d` (errno: 150 "Foreign key constraint is incorrectly formed") (SQL: alter table `users` add constraint `users_deleted_by_id_foreign` foreign key (`deleted_by_id`) references `users` (`id`))
at D:\xampp\htdocs\minside\v3\vendor\laravel\framework\src\Illuminate\Database\Connection.php: 664
660: // If an exception occurs when attempting to run a query, we'll format the error
661: // message to include the bindings with SQL, which will make this exception a
662: // lot more helpful to the developer instead of just the database's errors.
663: catch (Exception $e) {
664: throw new QueryException(
665: $query, $this->prepareBindings($bindings), $e
666: );
667: }
668:
669: return $result;
Exception trace:
1 PDOException::("SQLSTATE[HY000]: General error: 1005 Can't create table `laravel_026db`.`#sql-5ec_16d` (errno: 150 "Foreign key constraint is incorrectly formed")")
D:\xampp\htdocs\minside\v3\vendor\laravel\framework\src\Illuminate\Database\Connection.php : 458
2 PDOStatement::execute()
D:\xampp\htdocs\minside\v3\vendor\laravel\framework\src\Illuminate\Database\Connection.php : 458
Please use the argument -v to see more details.
Please note that the migration does create the users table with all the fields. However it is not able to create the constraint.
A bit late, but i could foreign UUID by adding primary() to my UUID
$table->uuid('id')->primary();
@TIDJ - Not too late
@NJOGUAMOS - Glad to help :)
behind scene of
$table->id();
is
$table->bigInteger('id')->primary();
but in uuid case you have to define these properties
$table->uuid('id')->primary();
From Laravel 7.15, you can now use $table->foreignUuid('deleted_by_id')->references('id')->on('users');
@Jahchap Was exactly my problem thank you
Please or to participate in this conversation.