Let's say I have this following table which has an enum field and a foreign to some target table:

Now I want to do some rules like following in my migration:

1. genre a with same target_id must be only one record
2. genre b with same target_id also must be only one record
3. the same target_ids must have only one record with is_primary:true
4. genre c can repeat in multiple records with same target_id

Is there any convenient way to do such thing with laravel Blueprint?

My migration:

$table->id();
$table->uuid('uuid')->unique()->default(DB::raw('UUID()'));
$table->enum('type', ['RECHARGE', 'CONVERT', 'FREEZE']);
$table->foreignId('target_id')->references('id')->on('targets');
$table->boolean('is_primary')->default(false);

I want my datum having only one FREEZE and one RECHARGE record per each target, but the CONVERT type can be multiple. and also I need only one of these records to be is_primary:true per each target.

CodePudding user response：

Let's start with:

3. the same target_ids must have only one record with is_primary:true

You could achieve this by having a multi-column unique index for target_id and is_primary and setting is_primary = NULL for those rows where uniqueness is not required (i.e. those that are false in your example).

$table->boolean('is_primary')->nullable();
$table->unique(['is_primary', 'target_id']);

The other constellation

1. genre a with same target_id must be only one record
2. genre b with same target_id also must be only one record
3. genre c can repeat in multiple records with same target_id

would require a partial index, which is supported by some (e.g. Postgres), but not all DBMS (e.g. not MySQL).

cf. https://en.wikipedia.org/wiki/Partial_index, https://www.postgresql.org/docs/current/indexes-partial.html