How use negative integer as primary key

@FabienArr Eloquent relations can work on any IDs (string, integer, uuid - whatever you want) as long as they uniquely represent rows in tables. If you have a custom ID that is out of conventions, you should explicitly set it in relation, for example:

$this->hasMany(RelatedModel::class, 'custom_foreign_key');

Your approach seems strange and unreliable, but it should work.

Why having positive and negative values? If you need to identify source of a row, you can set source column and Enum casting:

// enum
enum ApplicationSource: int
{
	case APP_MAIN      = 1;
	case APP_SECONDARY = 2;
}

// migration
$table->tinyInteger('source');

// model
$casts = [
	'source' => ApplicationSource::class,
];

Then app1 creates rows with source = ApplicationSource::APP_MAIN (or source = 1) and app2 creates rows with source = ApplicationSource::APP_SECONDARY (or source = 2) .

so to identify the sources and to don't have the same ID for 2 differents records, The first application have postiive ID and other the negative, and when we synchronize all will be ok.

@FabienArr And what happens if you get two records with the same negative ID? Or need to support a third producer of records? “Negative” IDs is not the solution. A unique value such as a UUID or ULID is.

Just because you use UUIDs or ULIDs to uniquely identify records coming from different sources does not mean you need to use UUIDs/ULIDs for primary keys, though. You can create a record using a UUID, and when you sync it, it gets assigned the next primary key in the sequence.

I did this for an EPOS project. The EPOS system needed to work offline, so it was impossible to keep IDs in sync because I wouldn’t know what the “latest” ID is without being able to ask the server, and other records may be created by other terminals that did have network connectively whilst one terminal was offline. So, the terminals just created orders with a UUID, and then when it regained network connectivity, would send the orders to the server to be inserted into the database (which used auto-incrementing IDs in its tables).

@FabienArr You want to have non-overlapping primary keys across two tables, right?

This is exact case for UUID (or ULID) as primary key, and Laravel supports it natively: https://laravel.com/docs/12.x/eloquent#uuid-and-ulid-keys

Another approach is auto-incrementing integer keys, but App1 assigns keys 1, 3, 5, 7... and App2 assigns keys 2, 4, 6, 8... As a result, primary keys never overlap. It is done on database level (google for mysql auto_increment_offset) and is used mainly for replication. This approach is more complicated, you will suffer setting it up, supporting and extending further.

In your approach I see at least two bad things:

1. how would you divide primary keys when you have three applications, not two? Positive keys go to App1, negative go to App2, nothing is left for App3, App4... etc.
2. Mysql can't produce auto incrementing (decrementing, to be precise) negative primary keys, as far as I know, you have to handle them yourself.

Why complicate your life? Use UUIDs.

@Glukinh Yes I want non-overlapping primary keys across two tables. Yes I know UUID, but UUID is unreadeable by human, it takes more spaces to store and I need to rewrite my synchronisation's module between the 2 applications because it uses integer. I didn't know mysql auto_increment_offset, I will google that.

For the 2 bad things, I'm agree.

I need to think about UUID, but 26 char for an ID it's quite big

@FabienArr If your app is so large that you have to estimate UUID column size, then you definitely shouldn't implement "auto-decrementing" negative primary key, reinventing database job by yourself.

@Glukinho I think I will use UUID because ULID doesn't exists in my other application. but the problem is that Laravel create UUID with the dash, and my application don't use the dash. Is it possible with Laravel to remove the dash in the database ?

@FabienArr There are two ways:

1. examine \Illuminate\Database\Eloquent\Concerns\HasUuids trait in Laravel source code and create your own:

trait HasUuidsWithoutDashes
{
    use HasUniqueStringIds;

    public function newUniqueId()
    {
        return (string) Str::of(Str::uuid7())->remove('-');
    }
}

Attach use HasUuidsWithoutDashes; to your model instead of HasUuids.

Note that HasUuids trait has isValidUniqueId() method which is not applicable to your situation (UUID without dashes is not valid UUID for Laravel, as function Str::isUuid($uuid_without_dashes) results in false). I don't know how it affects your app. Maybe you should implement it too:

protected function returnDashes(string $value): string
{
    // in:  019889e6ad48710f9305772d4cd1a0c1
    // out: 019889e6-ad48-710f-9305-772d4cd1a0c1

    $value = Str::of($value);

    return sprintf('%s-%s-%s-%s-%s',
        $value->substr(0, 8),
        $value->substr(8, 4),
        $value->substr(12, 4),
        $value->substr(16, 4),
        $value->substr(20, 12),
    );
}

protected function isValidUniqueId($value): bool
{
	return Str::isUuid($this->returnDashes($value));
}

2. Second approach is to create cast which removes dashes on set and adds them on get and apply it to your models' UUID fields: https://laravel.com/docs/12.x/eloquent-mutators#custom-casts

Both ways are non-standard, you should test them carefully.

@FabienArr

the problem is that Laravel create UUID with the dash, and my application don't use the dash

Is it a problem at all? These IDs will be of different size but they still don't overlap as long as your second app produces real UUIDs.

@Glukinho I thinked like you it was not a problem, but in fact in my application if I put primary as UUID primary key, I cant define how many char 36 vs 32, it's 32, so I can't integrate the UUID from laravel with their dashes. In the samtime, I found this https://stackoverflow.com/questions/65775661/how-to-generate-uuid-without-dashes-hyphens-in-laravel

What do you think about this solution ?

@FabienArr I mean, leave database field 36 chars long. Let Laravel put there UUID with dashes (36 chars) and your second app - UUID without dashes (32 chars). There is a chance it will work as you expect.

@FabienArr

What do you think about this solution ?

this is roughly the same as I proposed here: https://laracasts.com/discuss/channels/laravel/how-use-negative-integer-as-primary-key?reply=969215

instead of Str::of(Str::uuid7())->remove('-') you may use Str::uuid7()->getHex(), I suppose.

@Glukinho UUID generated by Laravel 019889c6-0948-72a5-aa81-3708630b2e0e
UUID generated my app : 4ECC362BD992AD4F8E121BBCC2727087

When I integrate the laravel UUID in my app, it is converted automatically like this : 019889C6094872A5AA813708630B2E0E

But if I need to resynchro this record to Laravel app, it will not match Iwith the initial record created in Laravel, It's what I supposed

@FabienArr Ok, so try to generate UUIDs without dashes in Laravel. Think about letter case too - maybe add ->upper() to a newUniqueId() method.

@Tray2 Yeah, this is an interesting scenario as we kinda end up with a polymorphic relationship.