1

So I have some tables in Sql Server that are essentially a list of sales, things like: 

ProductID 
SalesOrderID
ProductFamilyID 
ProductCost
ProductSource

and so on. In this case, none of the columns are necessarily unique, so I can't create a primary key from any combination of them. In fact, the only constraint that I really have on the table is that I need every row in the table to be a unique combination of the columns. So I'm assuming something like a unique index would be the way to go there.

The only primary key I could add is something like an autoincrement primary key. But what would be the actual use of that, database wise? What are the possible problems with not creating a primary key for a table like this?

Improve this question
4
  • 1
    So you can have two different ProductFamiyID for the same ProductID? And two seperate Product Costs for the same ProductID? Can you have many ProductIDs for the same SalesOrderID? Read about Functional Dependencies and normalization.
    – David Browne - Microsoft
    May 28, 2020 at 16:55
  • @DavidBrowne-Microsoft In this case, it may be possible to have different family's depending on the source of the ProductID, etc. I could possible have many ProductIDs for the same SalesOrderId. Part of my problem here is that I am unable to get a complete understanding of the data due to other reasons, so I am trying to limit the table as simply as possible. I will however, check out FD & normalization, thanks
    – ocean800
    May 28, 2020 at 17:08
  • 1
    Please add CREATE TABLE definition.
    – Lennart - Slava Ukraini
    May 28, 2020 at 18:57
  • I believe the best answer is the combination of both answers - the one from BCM and the one from Niels. The two together provide better guidance on Primary Keys and the use of a Surrogate Key (e.g. Identity column of sequential int values).
    – qxotk
    May 5 at 16:15

2 Answers 2

Reset to default
3

The big benefit is that the key is very narrow and naturally in order. Every non-clustered index you add by definition also contains the primary key in order to do look ups. So even though it doesn't appear to have much benefit, using an identity column as a PK would still be helpful.

Also, if you ever expand on to this and add other tables that need to reference this table, you just need the one column for the foreign key.

Improve this answer
2

Also, be aware that if you use an integer primary key (or unique constraint) there is no relation to the actual data. So that will mean that the data is physically sorted in a way that is possibly not very helpfull for your queries. A table is either a clustered index or a heap. A clustered index (thus the actual table) is sorted by the key that you configure. A heap is just a bunch of data in any order. So if you use an integer for your clustered index key AND do not have any other nonclustered index that will support your queries, then queries could be become very slow.

So not that you cannot use an integer as a constraint (primary key or clustered index), it is used a lot, but just be aware of the impact. Also, it would in general be better to always create at least one index, because then you would have statistics, which SQL server can determine a plan on. Preferably clustered because of the physical sort order which can support random queries more easily.

Here are some possible helpfull links that i found in my own library on the subject:

https://www.sqlpassion.at/archive/2015/06/29/primary-key-vs-clustered-index/

https://www.sqlshack.com/clustered-index-vs-heap/

https://www.brentozar.com/archive/2013/12/keep-it-constrained/

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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