Does postgresql require unique constraint names when defining FOREIGN KEYS

Question

Here is my schema for database tables:

 CREATE TABLE users (
  user_id INTEGER NOT NULL,
  device_id INTEGER,
  user_points INTEGER,
  cookie VARCHAR,
  PRIMARY KEY (user_id)
);

 CREATE TABLE admins (
   admin_id INTEGER NOT NULL,
   username VARCHAR,
   password VARCHAR
 );


 CREATE TABLE admin_adventure (
   adventure_id INTEGER NOT NULL,
   admin_id INTEGER,
   PRIMARY KEY (adventure_id)

 );

 CREATE TABLE adventures (
   adventure_id INTEGER NOT NULL,
   prize_id INTEGER,
   novella_id INTEGER,
   PRIMARY KEY (adventure_id)
 );

Here I'm trying to define FOREIGN KEYS:

ALTER TABLE  admin_adventure  ADD FOREIGN KEY ( admin_id ) REFERENCES  admins  ( admin_id );


ALTER TABLE  admin_adventure  ADD FOREIGN KEY ( adventure_id ) REFERENCES  adventures  ( adventure_id );

And here is the error I get when trying to migrate with Flyway:

ERROR: there is no unique constraint matching given keys for referenced table "admins"

Can someone explain what I'm doing wrong and why I get this error?

Answer 1

The error message is complaining that you are trying to link a foreign key to a column in another table which is not unique (e.g. a primary key). Try making the admin_id column in the admins table a primary key:

CREATE TABLE admins (
    admin_id INTEGER NOT NULL,
    username VARCHAR,
    password VARCHAR,
    PRIMARY KEY (admin_id)
);