Unindexed foreign key advise is misleading when there is a corresponding primary key. When compound primary key covers the foreign key, it shouldn't warn.
To Reproduce
Suppose you have following tables:
CREATE TABLE "public"."tenant" (
"id" uuid NOT NULL DEFAULT uuid_generate_v4(),
"name" text COLLATE "pg_catalog"."default" NOT NULL,
PRIMARY KEY ("id")
);
CREATE TABLE "public"."user" (
"tenant_id" uuid NOT NULL DEFAULT auth.tenant_id(),
"user_id" uuid NOT NULL,
"email" varchar COLLATE "pg_catalog"."default" NOT NULL,
PRIMARY KEY ("tenant_id", "user_id"),
FOREIGN KEY ("tenant_id") REFERENCES "public"."tenant" ("id") ON DELETE CASCADE ON UPDATE NO ACTION,
);
Advisor will warn you that there is no covering index for foreign key in user table. However, primary key covers it.
Expected behavior
It should detect that primary key covers the foreign key field(s) in the same order.
Bug report
Unindexed foreign key advise is misleading when there is a corresponding primary key. When compound primary key covers the foreign key, it shouldn't warn.
To Reproduce
Suppose you have following tables:
Advisor will warn you that there is no covering index for foreign key in user table. However, primary key covers it.
Expected behavior
It should detect that primary key covers the foreign key field(s) in the same order.