Non primary key relations

[ozum]

Feature description I would like to add a foreign key constraint referencing a unique constraint instead of the primary key. Best to describe with an example as below.

Both OptionCategory and Option tables have a single column primary key (id). OptionCategory table has a unique constraint ("id","businessId"). I would like to create a one to many relation using unique constraint ("id","businessId") as below:

OptionCategory Table

CREATE TABLE "OptionCategory" (
  "id" UUID DEFAULT gen_random_uuid() NOT NULL,
  "businessId" UUID NOT NULL,
);

ALTER TABLE "OptionCategory" ADD CONSTRAINT "Key16" PRIMARY KEY ("id");
ALTER TABLE "OptionCategory" ADD CONSTRAINT "KeyOptionCategoryBusiness" UNIQUE ("id","businessId");

Option Table

CREATE TABLE "Option" (
  "id" UUID DEFAULT gen_random_uuid () NOT NULL,
  "businessId" UUID NOT NULL,
);
ALTER TABLE "Option" ADD CONSTRAINT "Key13" PRIMARY KEY ("id");
ALTER TABLE "Option"
  ADD CONSTRAINT "OptionOptionCategory"
    FOREIGN KEY ("optionCategoryId", "businessId")
    REFERENCES "OptionCategory" ("id", "businessId")
      ON DELETE CASCADE
      ON UPDATE CASCADE;

Details

Currently, it is possible to manually add a Foreign Key constraint to Option table and manually select all referencing and referenced columns one by one as seen in the screenshot below.

However, we should be able to create this relation just by using "add one to many relation" and "selecting a key (constraint)". For example, we could select the key in the following form:

Below is a screenshot from another ERD tool showing this feature. You can select the parent key in relationship form. (Additionally it allows auto-creation of index for the foreign key)