Choosing appropriate primary key.
-
Please advise what would be the proper manner in selecting a primary key when there is no column(s)columns to assign as a unique identified column. I know about composite keys(selecting more than one column as a primary key). This also seem's not possible.
-
on those items mentioned, dv360_Line_Item_ID, Date and Event look unique - but you would need to check all your data.
normally you would analyze the data by doing group by in a set of columns filter (having) those with only count > 1
when it returns zero rows that is your possible unique key - but that may well be the whole record so obviously not feasible.
-
They are not unique.
-
I would recommend that you work with the business to identify the columns that make a unique row. They have to exist, otherwise how would you differentiate between two rows? Get the information from them. They know.
Then, honestly, I wouldn't make it my primary key. Looking at some combination of campaignid and date or whatever is needed, that's a pretty wide key and will make foreign keys in other tables also very wide. Absolutely make a unique constraint for the combination of columns that define the row. Then add an artificial key for use within the system. I'd go with either an INT and an IDENTITY value, or a GUID (which are just not as bad as people make out, although, not human readable). This makes all the foreign key values more narrow and aids performance.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore RooseveltAuthor of:
SQL Server Execution Plans
SQL Server Query Performance Tuning -
Can an aggregate column serve as a primary key?
- yrstruly wrote:
Can an aggregate column serve as a primary key?
You mean a computed column? Probably not the greatest of choices, but yes. As long as it's unique and the calculation that does it is deterministic (same every time). I think you probably also have to persist it, but I might be wrong about that.
Or, do you mean more than one column, a compound key?
Again, yes, but I usually reserve those for unique constraints only because they're wider (as I said above).
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore RooseveltAuthor of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply