sql - unable to add a foreign key

Question

I created this two table in a postgres database im trying to add the first uuid key in the second table as a foreign key but this error pops up

column "book_id" referenced in foreign key constraint does not exist

This is the first table

CREATE TABLE books(
book_id uuid DEFAULT uuid_generate_v4(),
book_title VARCHAR(255) NOT NULL,
book_categ VARCHAR(255),
book_price FLOAT
);

This is the second table where im trying to add the foreign key

CREATE TABLE reader(
reader_id uuid DEFAULT uuid_generate_v4(),
reader_fullname VARCHAR(255) NOT NULL,
reader_CIN VARCHAR(255),
reader_adress VARCHAR(255),
FOREIGN KEY (book_id) REFERENCES books(book_id)
);

Answer 1

You have multiple issues in your code. I would suggest:

CREATE TABLE books (
    book_id uuid DEFAULT uuid_generate_v4() PRIMARY KEY,
    book_title VARCHAR(255) NOT NULL,
    book_categ VARCHAR(255),
    book_price NUMERIC(20, 4)
);

CREATE TABLE readers (
    reader_id uuid DEFAULT uuid_generate_v4(),
    reader_fullname VARCHAR(255) NOT NULL,
    reader_CIN VARCHAR(255),
    reader_address VARCHAR(255),
    book_id uuid,
    FOREIGN KEY (book_id) REFERENCES books(book_id)
);

Notes:

• You need the primary key definition in the first table.
• You need to define the column in the second table.
• FLOAT is a bad choice for a monetary amount. You should use numeric/decimal.

I also don't know what you are trying to model. But my suspicion is that you want one table for readers with one row per "reader" and then another table for readerBooks with one row per book that a reader reads.