DB Design - Contextual constraints

Question

I have a pair of tables with parent-child relationship.

  domain:
     id int not null auto_increment primary_key
     domain varchar(100) not null

  domain_url:
     id int not null auto_increment primary key
     domain_id int not null
     path varchar(512)

Here I want to keep path unique with in one domain. Across the domain path can be duplicating. What is the best constraint to apply on path.

Should I focus on composite key between domain_id and path. Is composite key a reliable solution?

Answer 1

I think you do want to apply a unique constraint in domain_url table on the domain_id, path composite key. That will enforce your requirement "to keep path unique with in one domain"