Possible to create a constraint that is unique per foreign key?

Question

MySql, Table Repos Fk on Users.Id, A User can have many Repos however each Repo must have a unique Id, per User not per table, what is the best way to ensure that Repos Id is unique for a given User Id?

Thanks

Answer 1

Unique constrains, like any other index, can convert multiple columns in the same table - so to achieve what I think you are asking for you need to create a unique constraint on userId, repoId. This will enforce no user having multiple repos with the same identifier but will not stop another user having a record with the same repoId as one of mine.

The syntax for that would be something like ALTER TABLE table_name ADD CONSTRAINT constraint_name UNIQUE (userId, repoId);

Or you could create a unique index instead which is functionally identical. See When should I use a unique constraint instead of a unique index? for more discussion about that similarity and when you might choose one over the other.

^{Note: It might be a good idea to flesh out you question to describe what you are modelling in more detail, and what structure you currently have. It may be that a many-to-many relationship via a junction table is more appropriate, but I'll not dive deeply into that as it might easily be rendered completely irrelevant upon knowing more about your goal.}