sqlpostgresqldatabase-designnatural-key

Unique constraints on their own or as a primary key?


Is there any benefit to using a table schema like this:

CREATE TABLE review (
    review_id SERIAL PRIMARY KEY,
    account_id INT REFERENCES account(account_id) NOT NULL, 
    product_id INT REFERENCES product(product_id) NOT NULL, 
    rating SMALLINT NOT NULL, 
    comment TEXT,
    UNIQUE (account_id, product_id)
);

Or should the constraint itself be the primary key, like this:

CREATE TABLE review (
    CONSTRAINT review_pkey (account_id, product_id) PRIMARY KEY,
    account_id INT REFERENCES account(account_id) NOT NULL, 
    product_id INT REFERENCES product(product_id) NOT NULL, 
    rating SMALLINT NOT NULL, 
    comment TEXT,
);

Solution

  • The second version is clearly preferable, because it requires one less column and one less index, and there is no down side.

    The column is obvious, the indexes aren't, because you forgot to add them: You need indexes on all the foreign key columns so that deletes on the referenced tables can be fast. With the artificial primary key, you need indexes on review_id, account_id and product_id, while without you can do with the indexes on (account_id, product_id) and product_id.

    The only people who will advocate the first solution are people who hold a religious belief that every table has to have an artificially generated numerical primary key, no matter what. In reality, the combination of the two artificially generated keys from the referenced tables is just as good.