Laravel - there is no unique constraint matching given keys for referenced table - using multiple uniques

Question

I'm getting this error in Postgres 7.2:

Invalid foreign key: 7 ERROR: there is no unique constraint matching given keys for referenced table "holiday_items" (SQL: alter table "holiday_bookings" add constraint "holiday_items_parent_holiday_item_id_foreign" foreign key ("parent_holiday_item_id") references "holiday_items" ("id") on delete cascade)

Here is the migration I'm trying to run:

Schema::create('holiday_bookings', function (Blueprint $table) {
    $table->uuid('id')->primary();
    $table->uuid('booking_id')->index();
    $table->integer('booking_version')->index();
    $table->uuid('parent_holiday_item_id')->nullable();

    $table->unique(["id", "booking_id", "booking_version"]);

    $table->foreign('parent_holiday_item_id')->nullable()->references('id')->on('holiday_items')->onDelete('cascade');
    $table->foreign('quotation_id')->references('id')->on('quotations')->onDelete('cascade');

    $table->timestamps();
});

And here are the fields in the holiday_items table with a unqiue constraint:

Schema::create('holiday_items', function (Blueprint $table) {
    $table->uuid('id');
    $table->integer('booking_version')->index();
    $table->uuid('booking_id')->index();
    $table->timestamps();

    $table->unique(["id", "booking_id", "booking_version"]);

    $table->foreign(['booking_version', 'booking_id'])->references([
        'booking_version',
        'id'
    ])->on('bookings')->onDelete('cascade');

});

What is wrong with my foreign key assignment in the holiday_bookings migration?

Answer 1

you should set the id column as the primary key, this is where this column get uniqueness

in your migration:

 $table->uuid('id');
  $table->primary('id');

and there will be no need for setting this column as unique in your migration