What does it mean by "There is no primary key for this table, it may contain duplicates."?

ylyu1 0
Viewed 46

I know there is no unique column or columns in this table. But, does this mean there are more than one row with exactly same columns? OR does it only mean that some columns can have duplicate values but no exactly same rows in the table? Also, I use with cte as (select distinct (column1, column2...)" all the time to remove duplicate rows from these tables before joining other tables with primary keys...I feel like it may not be necessary but I am not sure. Could someone clarify this for me? Thanks!

I have this question because of this leetcode problem https://leetcode.com/problems/average-selling-price/. For the answer, I write select p.product_id, round(sum(units*price)/sum(units),2) as average_price from prices p join unitssold u on p.product_id = u.product_id and purchase_date between start_date and end_date group by p.product_id;

The submission is successful. But I wonder if the answer is still correct if there are duplicate rows in the unitssold table...As duplicate rows are calculated too, right? screenshot of the leetcode problem

duplicates mysql
1 Answers

With regards to your case, if you don't have a unique key (or primary key) on product_id then there can be more than row with the same product_id. The database engine will not check stop this from happening.

If you do have a unique key of product_id then the database will raise an error when to try to insert a row with a product_id if one such row already exists.

Rohit Gupta0
Related