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

Question

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

You are trying to reference public.provider_user (user_id). For that column to be referenceable it needs to be UNIQUE. Is it unique? — The Impaler
– The Impaler, Commented Feb 2, 2022 at 18:37

eshirvana · Accepted Answer · 2022-02-02 18:38:02Z

2

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

answered Feb 2, 2022 at 18:38

eshirvana

24.7k3 gold badges28 silver badges43 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

The Impaler Over a year ago

"...but the combination of those 3 column is..." -- very clear explanation, tbh.

jarlh Over a year ago

I'd rather say most common, instead of best practice - because both versions can be useful.

The Impaler Over a year ago

well... "FK to PK" is not quite a best practice, though. There's some freedom on that.

Ben_Sven_Ten Over a year ago

provider_user is actually a join table as well between providers and users, so the PK on that table is not what I am looking to populate. both FK's in volunteer_clients are references to User PKS. Please see updated code

Collectives™ on Stack Overflow

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

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related