4

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.

8
  • 2
    Look like You start from other side ... usually PK (or any unique index) based on business idea - what MUST be unique, and if data not unique - clean them. Why just not use new auto-indent column as PK if You can not decide ?
    – a_vlad
    Nov 16, 2015 at 15:01
  • 2
    What happens if you find the combination for one set of data, institute the key based on those columns then subsequently import more data that happens to contain rows with duplicate data for those columns? Sounds to me like you should re-think your design.
    – Hannah Vernon
    Nov 16, 2015 at 15:14
  • And "flat files from different sources" are almost certainly not going to be super reliable. Likely you will still need to do some currently unknown cleanup on the data before it is ready for use.
    – RLF
    Nov 16, 2015 at 15:30
  • @Max Vernon If that happens then my import process would fail at which point I would need to modify my primary key (probably by concatenating an additional column to the key to make it unique once again). If I had a script that could find the unique key (using available fields) I could do a check before every important to see if our key is still unique or not. I might need to rethink my process as you mentioned but I would still like to find out if anyone has ever used or created such a script. I am starting to think that such a script might use too many resources to be practical though...
    – Juan Velez
    Nov 16, 2015 at 15:30
  • 1
    Typically you create the primary key intentionally to support the best performance possible. What is enforcing uniqueness going to give you if you automate it? You might as well just create the PK on all columns.
    – Hannah Vernon
    Nov 16, 2015 at 15:33

4 Answers 4

4

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.

1
  • 1
    My data will go from my Import table into several other tables and the composite key will serve as a basis of supporting relationships to sibling and child tables. You do bring up several good points which lead me to believe it is probably not worth my time trying to create such a script. Thanks.
    – Juan Velez
    Nov 16, 2015 at 15:53
5

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.

enter image description here

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).

enter image description here

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. =)

5
  • That's awesome! Thanks! Definitely going to check it out.
    – Juan Velez
    Nov 16, 2015 at 17:20
  • 1
    This tool is particularly depressing. You've caveated at the end, but this is still enabling people to throw some stuff together without any knowledge of the data they're working with; sadface
    – Ben
    Nov 16, 2015 at 20:50
  • 1
    @Ben This tool does nothing except provide concrete knowledge about your data. It will never be as capable of leading people astray as ignorance.
    – Dave
    Nov 16, 2015 at 21:03
  • 1
    I mostly agree with @Ben here, especially the "sadface" part ;-). I think this tool is very interesting and is great to for gaining insight into data patterns. Sometimes that is very necessary to have but not easy to get. But, I think applying such info to this particular request is a bit dangerous because it doesn't get the O.P. what the O.P. truly needs, and that is rules about how the data exists in reality, either in the source system or what is required by this system for being imported. Nov 16, 2015 at 21:42
  • @PokerPlayer23 Please see my comment directly above (to Ben). As I said there, I think this tool is really neat and appreciate Dave for letting us (and others) know about it, but data profiling is not meant to suggest potential keys. If the goal is to determine what could be a PK based on the output of this tool, what you will walk away with is mainly a false sense of security. I just thought of 1 or 2 more potential issues with trying to determine uniqueness this way and will update my answer in a moment. Nov 16, 2015 at 21:45
0

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.

-1

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.

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.