0

I am trying to make a comment section for products. I am setting two primary keys, the CommentID and the ProductID for the Comments table. But what I want to do with the primary keys is as follows: for each ProductID, the CommentID should start counting from the beginning. So for ProductID = 1, the CommentID should start from 1 and be incremented, for ProductID = 2, the CommentID should also start from 1 and be incremented, and so on. Same time the ProductID is the foreign key that points on Product table.

Can someone guide me how to archive that, is it a good practice or does a better workaround exist except making two primary keys?

Thank you!

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Tinaira
  • 727
  • 2
  • 7
  • 23
  • You should learn what terminology means before you (mis)use it. A table can only have one Primary Key. https://en.wikipedia.org/wiki/Primary_key – Tab Alleman Jul 09 '18 at 19:33

1 Answers1

2

I would use IDENTITY column/sequence(single PK):

CREATE TABLE comments(comment_id INT IDENTITY(1,1) PRIMARY KEY,  
                      product_id INT REFERENCES Product(product_id),
                      some_text VARCHAR(1000),
                      -- ...
                      );

And selecting:

SELECT *,
   ROW_NUMBER() OVER(ORDER BY comment_id) AS rn 
  -- if you really need consecutive numbers
FROM comments
WHERE product_id = ?
Lukasz Szozda
  • 162,964
  • 23
  • 234
  • 275
  • a dumb question. how do I make an insert in this case? I mean, do I have to specify the PRoductID each time I make an insert! – Tinaira Jul 09 '18 at 20:13
  • And what does references do? how is it different from foreign key? – Tinaira Jul 09 '18 at 20:17
  • 1
    @Tinaira Yesm you have to specify ProductId. `REFERENCES` is inline version of foreign key. – Lukasz Szozda Jul 09 '18 at 20:22
  • OK. But as much as I see the CommentID will increment independently from the ProductID. So it will not start over when I chose a different Product to Comment – Tinaira Jul 09 '18 at 20:29
  • @Tinaira No, it won't. That is why I posted query with ROW_NUMBER. I guess you want it for showing in application. It is not DB concern at all. – Lukasz Szozda Jul 09 '18 at 20:30
  • ok. but the db should do as follow: for the selected productid to start the commentid from 1. So if I select product with id 1 and start making comments it should increment the commentid according to the product id. if I select productid=2 and start commenting it should reset the commentid for that particular product. so the commentid should be depending on the productid – Tinaira Jul 09 '18 at 20:38
  • @Tinaira No, it won't work that way. And I don't see a point of doing so. If you still insist you could calculate it on the fly with: `SELECT *, ROW_NUMBER() OVER(PARTITION BY productId ORDER BY comment_id) AS rn FROM comments`. Please note that comments may be deleted as well. Then you don't get consecutive numbers. – Lukasz Szozda Jul 09 '18 at 20:40
  • either there is a way that sql server does it autmatically or I have to first get the max commentid of the selected product, add 1 to it and set it as the commentid – Tinaira Jul 09 '18 at 20:41
  • @Tinaira `have to first get the max commentid of the selected product, add 1 to it and set it as the commentid`. I strongly recommend not doing so. It may fail with multiple concurrent transactions. – Lukasz Szozda Jul 09 '18 at 20:42
  • ok. but so, I must check if the commentid and productid already exist, like commentid=1 and productid=2 that it shouldn't get repeated. I want that both together work as one primary key – Tinaira Jul 09 '18 at 20:43
  • 1
    I understand your code now. The column you add in the select statement is just the highest commentid number regarding to the productid. It was at first a bit confusing because I expected something else, but now it makes sense – Tinaira Jul 22 '18 at 17:56