Column that must be unique only for rows with matching foreign key

Question

I'm trying to enforce a uniqueness constraint on a table and I'm not sure the best way to go about it. I have two tables: Parent and Child. Child.parent_id is a foreign key referencing Parent.id. I would like for Child.name values to be unique, but only for members of Child where the Child.parent_id value is the same. That is,

1. child_one.parent_id == child_two.parent_id && child_one.name == child_two.name should be impossible
2. child_one.parent_id != child_two.parent_id && child_one.name == child_two.name should be possible.

If it's a great deal of trouble I may be willing to relax the second constraint and simply say that Child.name must simply be globally unique, but I'm not very experienced with databases so I thought I would ask for advice.

I'm writing the application using Laravel, so the exact database technology is more of an implementation detail, but we happen to be using MySQL.

Thank you for any help.

Answer 1

I would like for Child.name to be unique, but only for members of Child where Child.parent_id is the same.

This mean you need NOT (Child.name) to be unique. You need in (Child.name, Child.parent_id) to be unique.