How to add foreign key in PostgreSQL

Question

I created this first table named 'bookstore' where Primary Key is book_name:

create table bookstore (book_name varchar primary key, author varchar, price decimal);

I am trying to create a second table named 'name' where name is primary key. I want to make this primary key- author.name as a foreign key of bookstore.author.

create table author (name varchar primary key, place varchar,
                    constraint fk_author_bookstore foreign key(name) references bookstore(author));

But the error is: ERROR: there is no unique constraint matching given keys for referenced table "bookstore" SQL state: 42830

I am new to SQL, so, hoping to get some help. If you can, please write the correct code. Thanks

Answer 1

Name column in author table is primary key and it's referenced as foreign key in bookstore table.

-- PostgreSQL (v11)

create table author (name varchar primary key, place varchar);

create table bookstore (book_name varchar primary key, author varchar, price decimal
, CONSTRAINT fk_author_bookstore
      FOREIGN KEY(author) 
      REFERENCES author(name));

Please check from url https://dbfiddle.uk/?rdbms=postgres_11&fiddle=8394f796433ed8bc170c2889286b3fc2

Add foreign key after table creation

-- PostgreSQL(v11)
ALTER TABLE bookstore
      ADD CONSTRAINT fk_author_bookstore FOREIGN KEY (author) 
          REFERENCES author (name);

Please check from url https://dbfiddle.uk/?rdbms=postgres_11&fiddle=d93cf071bfd0e3940dfd256861be813c