I need to know how to prevent duplicates in a join table.
I am building a restaurant inventory database. I have three tables: Restaurants, Products and a join table between them. The restaurant and the products tables both have numeric primary keys. The join table has foreign keys that are related to the respective parents. How do I prevent duplicates like the ones shown below?
|Restaurant ID||Product ID||Qty||My Comments|
|1||1||100||This is ok.|
|1||2||50||This is ok.|
|1||1||200||Not ok; this is a duplicate because the restaurant ID and product ID match the values in the first record.|
|2||1||50||This is ok.|
|2||2||75||This is ok.|