How do I make sure that all rows with the same foreign key have unique names in my PostgreSQL database?

I have a PostgreSQL database with two tables: Team and Project, in a one-to-many relationship.

Team has these columns:

• id
• name

Project has these:

• id
• name
• team_id

I’d like to make sure that the projects within a team must have unique names.

Projects belonging to different teams should be able to share names without a problem, so UNIQUE doesn’t really help.

It seems like I might be able to use a custom CHECK constraint to do this, but failing that, what would be a sensible, declarative way to implement it in javascript?

I’m using Prisma to interact with my database, and elsewhere in my app I’m using Yup to validate the schemas of objects. Perhaps I could combine these somehow?

Answer

You can create a unique constraint:

alter table project add constraint unq_project_teamid_name
    unique (team_id, name);

You can also do this with a unique index:

create unique index unq_project_teamid_name on project(team_id, name);