A foreign key, which has a unique constraint, should be reported as 1:1 relation

Simon_Schick · May 22, 2025, 11:02am

I’ll start with where I started - my SQL import of PostgreSQL:

CREATE TABLE public.routine_task_templates (
    routine_id uuid NOT NULL,
    task_definition_id uuid NOT NULL
);

ALTER TABLE ONLY public.routine_task_templates
    ADD CONSTRAINT routine_task_templates_pkey PRIMARY KEY (routine_id);

CREATE TABLE public.task_definitions (
    id uuid DEFAULT gen_random_uuid() NOT NULL
);

ALTER TABLE ONLY public.task_definitions
    ADD CONSTRAINT task_definitions_pkey PRIMARY KEY (id);

ALTER TABLE ONLY public.routine_task_templates
    ADD CONSTRAINT "routine_task_templates-unique-task_definition_id" UNIQUE (task_definition_id);

ALTER TABLE ONLY public.routine_task_templates
    ADD CONSTRAINT routine_task_templates_task_definition_id_fkey FOREIGN KEY (task_definition_id) REFERENCES public.task_definitions(id) ON DELETE CASCADE;

This generates the following definition:

Table "routine_task_templates" {
  "routine_id" uuid [pk, not null]
  "task_definition_id" uuid [unique, not null]
}

Table "task_definitions" {
  "id" uuid [pk, not null, default: `gen_random_uuid()`]
}

Ref "routine_task_templates_task_definition_id_fkey":"task_definitions"."id" < "routine_task_templates"."task_definition_id" [delete: cascade]

By reading the SQL, I would expect the system to report the relation as 1:1 relation, but instead I see the system reporting it as a 1:n relation: