have UUID and ID at same table

hello , I want have the UUID but I want to keep the id at the table . I saw this video - https://www.youtube.com/watch?v=jCO3KLQBGw4

that gives an a example that I have to do , when create a new record . it will creant a new UUID and ID .

it is like that -

  public function up()
    {
        Schema::create('user', function (Blueprint $table) {
            $table->increments('id');
            $table->uuid('uuid')->default(DB::raw('(UUID())'))->index();

            $table->string('email')->unique();
            $table->string('password');
    
            $table->timestamps();
        });
    }

But when I try to run the migration , it gives this error -

    SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(UUID()), `email` varchar(255) not null, `password` varchar(255) not null, `reme' at line 1 (Connection: mysql, SQL: create table `user` (`id` int unsigned not null auto_increment primary key, `uuid` char(36) not null default (UUID()), `email` varchar(255) not null, `password` varchar(255) not null, `

LaryAI

2 years ago

Level 58

It looks like you're trying to set a default value for the uuid column using the MySQL UUID() function directly in your Laravel migration. However, the syntax error you're encountering is likely due to the way you're trying to set the default value.

To fix this, you can use a raw expression within the default method. Here's how you can modify your migration to correctly set the default value for the uuid column:

public function up()
{
    Schema::create('user_admins', function (Blueprint $table) {
        $table->increments('id');
        $table->uuid('uuid')->default(DB::raw('UUID()'))->index();

        $table->string('email')->unique();
        $table->string('password');

        $table->timestamps();
    });
}

Make sure that you have the uuid method available in your version of Laravel. If it's not available, you can define the uuid column as a string with a length of 36 characters:

$table->char('uuid', 36)->default(DB::raw('UUID()'))->index();

Also, ensure that your database user has the necessary privileges to execute the UUID() function.

After making these changes, you should be able to run your migration without encountering the syntax error. Remember to run the migration with the following command:

php artisan migrate

This will create a new table user_admins with both an auto-incrementing id and a uuid that is automatically generated for each new record.

KalimeroMK

2 years ago

Level 41

The error you're encountering when running the migration is due to the incorrect syntax used for setting a default value for the uuid column. In MySQL, you cannot directly use a function like UUID() as the default value for a column.

To work around this, you have a couple of options:

Option 1: Use Laravel's Model Events Instead of setting the UUID in the migration, you can do it in the model's creating event. This ensures that a UUID is generated whenever a new model instance is created. Here's an example:

Migration:

public function up()
{
    Schema::create('users', function (Blueprint $table) {
        $table->increments('id');
        $table->uuid('uuid')->unique();
        $table->string('email')->unique();
        $table->string('password');
        $table->timestamps();
    });
}

Model (User.php):

use Illuminate\Database\Eloquent\Model;
use Illuminate\Support\Str;

class User extends Model
{
    protected static function boot()
    {
        parent::boot();

        static::creating(function ($model) {
            $model->uuid = (string) Str::uuid();
        });
    }
}

Option 2: Use a Database Trigger Create a database trigger to automatically generate a UUID when a new record is inserted. This is a bit more complex and depends on your database system.

Option 3: Modify Your Migration Another way (not recommended) is to modify your migration to set the UUID after the record is created. This approach is less clean and can lead to issues with race conditions or bulk inserts.

Note on Usage: Make sure that the users table is named correctly (plural, as per Laravel's convention). Ensure that the Str facade is imported in your model file if you're using Option 1. Using a UUID as a primary key or a unique identifier is a common practice, especially in distributed systems, but keep in mind that it can have performance implications compared to integer-based keys in larger databases.

Please or to participate in this conversation.