I have a database with let's say two tables as follows:

Products Table
ProductID | ProductName

and

Widgets Table
WidgetID | ProductID (Foreign Key) | Position


Now how do I set it up if I want a variable number of widgets that attach to a product. But I want to ensure uniqueness in the Position Column WHERE ProductID is XYZ.


So I want to be able to have the following entries in Widgets:

0 | 1 | 1
1 | 1 | 2
2 | 2 | 1
3 | 1 | 3

But not:

0 | 1 | 1
1 | 1 | 2
2 | 1 | 1
3 | 1 | 3

I'm using MySQL but I'm open to other databases. Or do I just need to handle this on the application side to check for a previous entry. I don't mind doing this either, but if there is a way to ensure that no duplicates get entered at all costs would be idea.

Thanks!
Gavin