This is a little embarrassing, considering how long I've been working with SQL, but it's important to never stop learning.
Apparently, you can have a unique constraint that encompasses multiple columns. This is super useful!
I had a problem at work where I have two tables, a parent table
PARENT -------------- ID
And a child table
CHILD ------------- ID PARENT_ID NAME
I wanted to have each parent have children with unique names. Children don't need to have unique names - you might have a classroom full of Aidens, but every family only has one Aiden. I totally blanked on how to do this. I thought I probably had to craft a check constraint with a query that looked up the parent, but Stack Overflow to the rescue
You can just make a unique constraint that covers PARENT_ID and NAME at the same time
ALTER TABLE child ADD CONSTRAINT multi_unique UNIQUE (parent_id, name)
This probably saved future me from a real pain in the ass - I was worried I'd have to enforce this solely with business rules in the middle tier.