multiple foreign keys as primary key postgres, should I do it?

This is one of those: why I should or why I should not.

So my books app has reviews, but one user must not review the same book more than one time. In my point of view makes sense to create a table for the reviews and make the user_id and the book_id (ISBN) as the PRIMARY KEY for the reviews table. But, could it be a problem at some point if the application gets too many reviews, for example, could that decision slow the queries?

I am using postgres and I am not sure if the following code is correct:

CREATE TABLE users(
    user_id PRIMARY KEY SERIAL,
    user_name VARCHAR NOT NULL UNIQUE,
    pass_hash VARCHAR NOT NULL,
    email VARCHAR NOT NULL UNIQUE,
);

CREATE TABLE books(
    book_id PRIMARY KEY BIGINT,
    author VARCHAR NOT NULL,
    title VARCHAR NOT NULL,
    year INT NOT NULL CHECK (year > 1484),
    review_count INT DEFAULT 0 NOT NULL,
    avrg_score FLOAT,
);

CREATE TABLE reviews(
    user_id INT FOREIGN KEY REFERENCES users(user_id) NOT NULL
    book_id INT FOREIGN KEY REFERENCES books(book_id) NOT NULL
    score INT NOT NULL CHECK (score > 0, score < 11)
    PRIMARY KEY (book_id, user_id)

);

Answer

This is a perfectly valid design choice.

You have a many-to-many relationship between books and users, which is represented by the reviews table. Having a compound primary key based on two foreign keys lets you enforce referential integrity (a given tuple may only appear once), and at the same time provide a primary key for the table.

Another option would be to have a surrogate primary key for the bridge table. This could make things easier if you need to reference the reviews from another table, but you would still need a unique constraint on both foreign key columns for integrity, so this would actually result in extra space being used.

When it comes to your code, it has a few issues:

• the primary key keyword goes after the datatype

• the check constraint is incorrectly formed

• missing or additional commas here and there

Consider:

CREATE TABLE users(
    user_id SERIAL PRIMARY KEY ,
    user_name VARCHAR NOT NULL UNIQUE,
    pass_hash VARCHAR NOT NULL,
    email VARCHAR NOT NULL UNIQUE
);

CREATE TABLE books(
    book_id BIGINT PRIMARY KEY,
    author VARCHAR NOT NULL,
    title VARCHAR NOT NULL,
    year INT NOT NULL CHECK (year > 1484),
    review_count INT DEFAULT 0 NOT NULL,
    avrg_score FLOAT
);

CREATE TABLE reviews(
    user_id INT REFERENCES users(user_id) NOT NULL,
    book_id INT REFERENCES books(book_id) NOT NULL,
    score INT NOT NULL CHECK (score > 0 and score < 11),
    PRIMARY KEY (book_id, user_id)

);