1

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.

3
  • Is there any reason a UNIQUE NON-CLUSTERED index on those two columns wouldn't work? Nov 16, 2018 at 15:25
  • @Akina that's exactly what I was looking for! I knew this was the perfect question for a StackExchange site. Something relatively simple for someone with experience, but difficult to google for if you don't know the right words! Care to write up an answer? Nov 16, 2018 at 15:33
  • No problems... moved.
    – Akina
    Nov 16, 2018 at 15:35

1 Answer 1

2

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.