make column unique in context of one foreign key entity

So let’s image a database scheme for todos. Your todo table would be

• id (PK)
• owner_id (Foreign key on user table)
• title

Of course the title column can’t be unique because each user can have a todo called “Do this”. But one user should not have two todos called “Do this”. So title is not unique but it is unique for one user (foreign key).

Is there a way I can achieve this by database design?

Answer

Define a unique constraint or index:

create unique index unq_t_owner_id_title on t(owner_id, title);