SQL query returns 'no unique constraint' but table DOES show unique constraint-CodePudding

I am trying to create a join table between volunteers and clients in SQL:

CREATE TABLE IF NOT EXISTS public.volunteer_client
(
  id serial PRIMARY KEY,
  volunteer_id integer NOT NULL,
  client_id integer NOT NULL,
  created_by text,
  created_at timestamp NOT NULL DEFAULT now(),
  updated_by text,
  updated_at timestamp NOT NULL DEFAULT now(),
  CONSTRAINT fk_volunteer_client_volunteer FOREIGN KEY (volunteer_id)
      REFERENCES public.provider_user (user_id) MATCH SIMPLE
      ON UPDATE CASCADE
      ON DELETE RESTRICT,
  CONSTRAINT fk_volunteer_client_client FOREIGN KEY (client_id)
      REFERENCES public.provider_client (user_id) MATCH SIMPLE
      ON UPDATE CASCADE
      ON DELETE RESTRICT
);

ALTER TABLE public.volunteer_client OWNER to navigate;

The error I am currently receiving:

ERROR:  there is no unique constraint matching given keys for referenced table "provider_user"
SQL state: 42830

Although in my provider_user table the user_id does appear to have a unique constaint:

CREATE TABLE IF NOT EXISTS public.provider_user
(
  id serial PRIMARY KEY,
  provider_id integer NOT NULL,
  user_id integer NOT NULL,
  user_type_id integer NOT NULL,
  created_by text,
  created_at timestamp NOT NULL DEFAULT now(),
  updated_by text,
  updated_at timestamp NOT NULL DEFAULT now(),
  UNIQUE(user_id, provider_id, user_type_id),
  CONSTRAINT fk_provider_users_provider FOREIGN KEY (provider_id)
      REFERENCES public.provider (id) MATCH SIMPLE
      ON UPDATE CASCADE
      ON DELETE RESTRICT,
  CONSTRAINT fk_provider_users_user FOREIGN KEY (user_id)
      REFERENCES public.user (id) MATCH SIMPLE
      ON UPDATE CASCADE
      ON DELETE RESTRICT,
  CONSTRAINT fk_provider_user_type FOREIGN KEY (user_type_id)
      REFERENCES public.provider_user_type (id) MATCH SIMPLE
      ON UPDATE CASCADE
      ON DELETE RESTRICT
);

Any help would be greatly appreciated!

UPDATE:

CREATE TABLE IF NOT EXISTS public.volunteer_client
(
  id serial PRIMARY KEY,
  volunteer_id integer NOT NULL,
  client_id integer NOT NULL,
  created_by text,
  created_at timestamp NOT NULL DEFAULT now(),
  updated_by text,
  updated_at timestamp NOT NULL DEFAULT now(),
--   UNIQUE(volunteer_id, client_id),
  CONSTRAINT fk_volunteer_client_volunteer FOREIGN KEY (volunteer_id)
      REFERENCES public.user (id) MATCH SIMPLE
      ON UPDATE CASCADE
      ON DELETE RESTRICT,
  CONSTRAINT fk_volunteer_client_client FOREIGN KEY (client_id)
      REFERENCES public.user (id) MATCH SIMPLE
      ON UPDATE CASCADE
      ON DELETE RESTRICT
);

ALTER TABLE public.volunteer_client OWNER to navigate;

Both volunteer_id and client_id FKS make reference to a PK on Users table

CodePudding user response：

first, You have a composite unique key on (user_id, provider_id, user_type_id) so user_id by itself is not guaranteed to be unique , but the combination of those 3 column is.

second, your primary key in provide_user is column id , and the best practice is to have FK to PK , not to the uniuque column(s)

so instead of having volunteer_id linked to user_id from provider_user, link it to id column