Search code examples
sqlpostgresqlcreate-table

PostgreSQL unique by two columns

The table:

id SERIAL, 
certificates_path VARCHAR,
voyages_path VARCHAR,
general_path VARCHAR,
user INTEGER REFERENCES users(id),
vessel INTEGER REFERENCES vessels(id);

Each users has it's own set of paths for each vessels.

certificates_path, voyages_path and general_path hold standard Linux/Windows file paths.

I need to CREATE TABLE and ensure that only one record for each user-vessel combination would exist.

Solution

  • Add a unique constraint:

    create table . . . (
    . . . ,
    constraint unq_t_paths unique (user, vessel)
);