-2

I have a recipe table that is structured like this:

product2recipe
id | productid | recipeid

I want to eliminate insertion of duplicate values. Basically a recipe can contain 1 or more productid. So it looks like this:

1 | 1 | 1
2 | 2 | 1
3 | 1 | 2
4 | 5 | 3

The user can submit a new recipe, if the recipe contains similar value like: 

id | 1 | 4
id | 2 | 4

Then it should not submit, because the table already have duplicate values, recipeid 1 contains productid 1 and 2 already. If it is:

id | 1 | 5
id | 3 | 5

Then it should submit since this is a unique combination.

How can I query my table optimally? I'm afraid that it might stall my database server since there's possibly many combinations.

John Woo
  • 258,903
  • 69
  • 498
  • 492
samuelmrrsn
  • 3
  • 2
  • Are you looking for a query to write that would return all duplicate recipes (or just a count of duplicate recipes), or do you want a DB constraint that would prevent the insertion of duplicate recipes? (I'm not sure the latter is possible.) –  Apr 10 '13 at 13:38
  • Incidentally, is this actually going to be a database of recipes, or is "recipe" just an example of a similar type of data organisation? –  Apr 10 '13 at 14:19

2 Answers2

3

The example you have provided does exactly tells us what you want because those records does not exist on the table.

But anyway, here's a way to enforce a UNIQUE constraint for compound column in your table,

ALTER TABLE product2recipe ADD CONSTRAINT tb_uq UNIQUE(productid, recipeid)

uncomment the ALTER TABLE statement in the demontration link above and see what happens

John Woo
  • 258,903
  • 69
  • 498
  • 492
  • is it exactly what you are looking for? `:)` – John Woo Apr 08 '13 at 05:41
  • no worries `:D` if this doesn't work, let me know so i can update it `:)` – John Woo Apr 10 '13 at 13:22
  • let's talk here, what do you mean by more rows? – John Woo Apr 10 '13 at 13:33
  • Unique statement will only work for 2 combinations. example [productid, recipeid] [1,1] and [1,1] would not work. However my problem is not allowing users to submit [1,2] since productid [1,1] is already there so it will just create another recipe with similar ingredient – samuelmrrsn Apr 10 '13 at 13:38
  • I believe @samuelmrrsn doesn't want a row-based unique constraint, but a recipe-based unique constraint. Recipe 1 is products [1,2] so, no new recipe can be added which is [1,2] only. – drquicksilver Apr 10 '13 at 13:40
  • I want this to not work. http://www.sqlfiddle.com/#!2/12b564 since recipeid 1 contains both ingredients already – samuelmrrsn Apr 10 '13 at 13:48
  • `1,4` and `2,4` doesn't exist. why don't you want to insert it? – John Woo Apr 10 '13 at 13:50
  • If i need to create more table to enforce this or more columns to enforce this then it is possible. Because i just started to build the site so i won't need to go great lengths on updating it. – samuelmrrsn Apr 10 '13 at 13:52
0

Given a known set of products for a "new" recipe, I would suggest running the following query:

select recipeid
from product2recipe
group by recipeid
having count(distinct productid) = 
       count(distinct case when productid in (?,?,...) then productid end)

- any recipes returned will have the same set of products as the "new" recipe; your application should then be programmed to reject such "new" recipes.

  • icic, will it be slow to run this query on a huge number or recipes? I mean is this the best way to enforce my structure? – samuelmrrsn Apr 10 '13 at 14:10
  • @samuelmrrsn: How huge is "huge"? It's likely to be dependent on a number of factors, such as number of rows, database server memory, disk speed, etc - the best way to check would be to run test queries against a test database with a realistic number of entries, as this is likely to perform more slowly as the number of entries increases. –  Apr 10 '13 at 14:15
  • ic.. hmm ill just wait and figure it out next time since this is just a local website in our country only so i don't think it will gain a lot of visitors. so i think this will just work fine. Thank you! – samuelmrrsn Apr 10 '13 at 14:47