0

This might be too subjective, but it's been puzzling me some time.

If you have a Fact table that allows duplicates with 10 dimensions that do not, do you really need a primary key?

Why Are There Duplicates?

It's a bit tricky, but ideally each duplicate is actually valid. There is just not a unique identifier to separate them from the source system recording the record. We don't own that system so there is no way to ever change it.

Data

The data is in batch and only include the previous days worth of records. Therefore, in the event of a republish. We just drop the entire days worth of records and republish the new day of records without the use of a primary key.

This is how I would fix bad data.

Generate A Primary Key Already

I can, but if it's never used or have anyway to validate if the duplicate is legit, why do it?

Improve this question
4
  • You seem to answer your own question If you have no need for it, then you don't need it. How can someone on SO really tell you what you need? Commented Aug 13, 2015 at 14:38
  • 1
    There are some things you can not do (or are extremely complicated) if you don't have a primary key, for example, lets say you have 5 duplicate rows, how would you delete 3 of them? Commented Aug 13, 2015 at 14:42
  • Thanks DavidG. I'll add the reason why in the question, but to be short. Data is in batch from the previous day. So, when I import the data, it drops the entire day of records I'm importing for and imports the new set of data. Commented Aug 13, 2015 at 14:48
  • 2
    @DavidG DELETE TOP(3)? Commented Aug 13, 2015 at 14:51

2 Answers 2

Reset to default
1

SQL Server database tables do not require a primary key.

A database engine may well create a primary key in the background though.

Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

I know it doesn't on the server level, but should I keep it that way?
Personally I can't see why not - your table is unusual - but I haven't done any serious database programming for some years now.
Yeah, I think it's the one case I've experienced where it's fine. Other time, row level uniqness is needed to ensure no duplicates exist.
0

Yes, SQL Server don't need primary key. Mostly, it needs in CLUSTERED index. Because, if you have another NONCLUSTERED indexes on this table, every of them will use CLUSTERED index for pointing data. So, primary key is good example of clustered key. And if it's short, and you have another indexes - it's reason to create it.

Improve this answer

1 Comment

I clustered this on time.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.