1

According to this SO Answer, there is no unique constraint matching given keys for referenced table "..." is an error you get when you make a foreign key reference call to a column which is not unique. But why am I getting this error here?

postgres=# create database example;
CREATE DATABASE

postgres=# \c example
You are now connected to database "masterdb" as user "postgres".

masterdb=# create table foo(
id bigserial
);
CREATE TABLE

masterdb=# create table bar (
id bigserial,
foo_fid bigint not null references foo (id)
);
ERROR:  there is no unique constraint matching given keys for referenced table "foo"
puk
  • 16,318
  • 29
  • 119
  • 199
  • 1
    The fk must reference a key, but the foo column id is not a key. – jarlh Sep 20 '20 at 18:33
  • @jarlh I thought `BIGSERIAL` implied `PRIMARY KEY` – puk Sep 20 '20 at 18:35
  • 1
    "_It is important to note that the SERIAL does not implicitly create an index on the column or make the column as the primary key column._" https://www.postgresqltutorial.com/postgresql-serial/ – jarlh Sep 20 '20 at 18:37
  • 2
    `BIGSERIAL` is just a psuedo-type [serial](https://www.postgresql.org/docs/current/datatype-numeric.html#DATATYPE-SERIAL) that generates numbers from a sequence. You need to specify `PRIMARY KEY` or `UNIQUE` if you want that column to be used by a `FOREIGN KEY`. Same applies to `GENERATED AS IDENTITY`. – Adrian Klaver Sep 20 '20 at 18:39

1 Answers1

4

Your table has to have unique index on the referenced column:

CREATE TABLE foo(
    id BIGSERIAL UNIQUE
);
Vesa Karjalainen
  • 1,087
  • 8
  • 15