1

I have a table like this

 ID | PRODUCT_NAME | MANUFACTURER_ID | PACKAGE_ID
---------------------------------------------------
  1 |   VITAMIN A  |       1         |      1
  2 |  PARACETAMOL |       2         |      1

PRODUCT_NAME must be unique for same manufacturer and same package ID, BUT it can be duplicate if manufacturer ID or Product ID are different. I tried using UNIQUE but it is rejecting same values with different Manufacturer ID or Package ID

My desired output would be like :

 ID | PRODUCT_NAME | MANUFACTURER_ID | PACKAGE_ID
---------------------------------------------------
  1 |   VITAMIN A  |       1         |      1
  2 |  PARACETAMOL |       2         |      1
  3 |   VITAMIN A  |       2         |      1

Notice third value of PRODUCT_NAME 'VITAMIN A' is same but MANUFACTURER_ID is different. Do we have any work-around for this other than UNIQUE?

  • Create a UNIQUE index on the combination of both product and manufacturer! p.s. welcome to the forum! :-) p.p.s. shown here. – Vérace Sep 4 at 7:35
  • Thanks alot, this solution saved me big time. I was going in a non ethical way. I tried to put Primary key on all except for main ID. So whenever I enter duplicate, it would throw me error. But your solution is very ethical and works best for me. Thanks alot again. Appreciate it. :) – Sushant Sep 4 at 7:52
  • I can write it up as an answer if you would like? – Vérace Sep 4 at 7:54
  • 1
    It seems you want a UNIQUE constraint/index on (PRODUCT_NAME, MANUFACTURER_ID, PACKAGE_ID) – ypercubeᵀᴹ Sep 4 at 7:55
  • @ypercubeᵀᴹ Yes, thats the solution. – Sushant Sep 4 at 8:05
0

I think the problem here is that you were unaware that UNIQUE constraints can contain more than one field. Based on your question, I suggested 2 fields, i.e. both product and manufacturer.

It appears now (following comments) that 3 are required, as originally suggested by @ypercube.

From the link I originally posted here, the syntax you require is as follows:

CREATE UNIQUE INDEX 'my_product_uq'  
ON my_product (product_name, manufacturer_id , package_id);

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.