Programmatically finding minimum set of fields necessary to create a unique composite key

Question

I am importing flat files from different sources into tables in SQL Server. I am creating a composite primary key using a combination of fields from the extracts that will give me a unique key for every row.

The way I do it now is I just start with 1 field and then I keep concatenating fields until I find a key that is unique for all records. This can be a little time consuming or I might end up concatenating more columns than I really needed to in order to obtain the unique key.

Is there some sort of SQL script I can run on a table that will give me the minimum number of fields (names) I would need to concatenate in order to obtain a unique key? So if there is 1 field in the table that is unique for all records then that 1 field name would be returned. If I needed to concatenate [memberid], [claimid], and [date of service] in order to obtain a unique key then those 3 field names would be the result of the script.

Answer 1

I am creating a composite primary key using a combination of fields from the extracts that will give me a unique key for every row.

Um, this is not exactly what a Primary Key is for. Yes, they do uniquely identify each row, but they also are the basis of supporting relationships to sibling and child tables.

Is there some sort of SQL script I can run on a table that will give me the minimum number of fields (names) I would need to concatenate in order to obtain a unique key?

Not outside of what you are already doing, although maybe in slightly different forms, such as possibly loading the data into a table with no keys or unique indexes or unique constraints defined, and then trying to create the PK or Unique (Index | Constraint) on various combinations of fields. In either case you probably shouldn't be doing this in the first place.

There are a few problems with this approach in general:

• What if there are several options for uniqueness? Any combination of one or more individual fields and / or one or more sets of multiple fields? FieldA could be unique, and FieldD + FieldH could be unique. What then?
• What if no individual fields or combinations of fields are unique? What is the plan if using all of the fields still doesn't get you uniqueness?
• What about bad data and/or errors in your import process? Scanning the incoming data is highly error prone due to not knowing how much you can trust the data to begin with. Your system needs to have some understanding if the incoming data is good or bad, not merely assume it to be good and base business rules around what could just as easily be a bug in the external system's export process (cuz that neeeeeeever happens ;-).
• Should any of these import tables relate to each other? If so, trying to find uniqueness programmatically might pick different fields for tables that should relate but now can't due to uniqueness being found in multiple combinations of fields in at least one of the tables, and the process finding an incompatible combination prior to the relatable combination.
• How are you deciding what the types are? What about 0x02FB4C97? Is that a VARBINARY or a string of hex bytes? What about 123456? Is that an INT, BIGINT, VARCHAR, DATETIME (in Julian format), VARBINARY (without the leading 0x but no A - F to help decide)?
• What happens when the "data" in one or more columns radically changes but for good / natural reasons? What if there is a field that is currently empty and you think it is a string but it really is a date field that hasn't been used yet, or naturally is NULL for the particular export you are looking at? Or what about max lengths for variable length columns? What about a "comments" field that they are currently just using for a 5 digit number, but later start using it for actual comments?
• As @MaxVernon mentioned in a comment on the Question, the uniqueness of the fields can change as new data comes in later since you don't really know the rules that govern how the data truly exists.

So this comes down to: what is the actual goal of defining the PKs to begin with? What are you trying to accomplish by doing this? Is there a reason why you don't just add an IDENTITY field and then remove duplicates within all of the imported fields (all minus the IDENTITY field)?

You really need to find out more about the true nature of the data first, and then build a table to hold the data with keys and constraints that match how the data should exist, not necessarily how it does exist.

Answer 2

While comments and srutzky offer some great advice, there is a tool made exactly for your situation. The SSIS Data Profiling Task is intended for the purpose of identifying potential Primary Keys (for multiple columns) along with providing a lot of other useful insights into your data.

Simply create a new SSIS package, add the task then use the wizard like interface to profile your data. Create a new output file somewhere you can access it, select Quick Profile... then profile the appropriate information from the database and table desired.

Once you're done run the package and return to the component to select Open Profile Viwer... and look at all the exciting data! The tool gave me a 96% match for one of my fact tables for a three column PK when up to 7 columns were requested for the candidate key consideration (not shown).

Just to be clear I definitely agree that the business rules should determine data uniqueness... just because you find a combination of columns that fits over your data for uniqueness doesn't necessarily mean it makes any sense. =)

Answer 3

Do not think that there is a script for this. It needs to be decided/defined before the data is entered. Otherwise it might block your application from functioning.

Normally a table needs 1 field to be unique. Only if you create a table to link 2 different tables (for an N-to-M) relation then you need both the M and the N key to make it unique. There are exceptions but it is up to the designer to decide what field(s) make up the unique key. The data can grow and so can the uniqueness.

Answer 4

Although I agree with some posts that keys should be defined by the business case this seems to me the view from the perspective of a database administrator.

From the perspective of data analytics where you may face a static data set you want to analyze such key combinations might be interesting and useful. Imagine a situation where you found a subset of data and you are wondering what the key fields are which determine it. For instance let's assume you found a subset of orders for some combination of products and you wonder what the parameters which define these orders best.
Of course you could define them by their order ids but there might be a smaller and more interesting key combination such as the age of the customer and the time the order was made.

That's a typical clustering/classification problem.