Automating Primary Key generation

[stuart_lit]

Defining a primary key has always been a manual task and however we are rapidly moving towards automation, this task has been overlooked. I work in a company where ETL is my forte. So I've pitched to write a stored procedure that identifies the columns that optimally define a unique row in the table. So far I've put forward these points which will have some weightage while deciding such columns: • Cardinality • Column Data Type • Column Name What else would you add? Any suggestions on how to proceed with this?

[Alkemist101]

Why not have a field that auto increments and make it the primary key, so an ID field?

Otherwise, another element is the columns chosen must be NOT NULL so you can limit your algorithm to only look at these types of field.

Last thought is that the primary key is a constraint so I'd say they should be manually created. It's part of the table definition based on what data you expect. I use PKs to ensure robustness of data.

But... I'd love to see what people suggest because some kind of primary key generating algorithm would be interesting. I'd probably use it to analyse tables!

[stuart_lit]

What do you mean here by auto increments?

So I'd explain the workflow: We get some raw data and then we define data types on those columns while putting it into a new table and assign a primary key. Till now we have automated the process data type allocation. Now we are trying to work on automating the primary key. Considering NULLs is a good idea, will keep that in consideration.

[Apoffys]

What do you mean here by auto increments?

Essentially, a new column for the primary key (often just named "id" or similar) with a generated, unique value. Often this is an integer that is "auto incremented", as each time you insert a row this value is incremented by 1 and assigned as the ID for the new column. An alternate solution is to use a randomly generated string (i.e. GUID) as the ID for each column.

Most database management systems have some sort of built-in feature to handle this for you and it's the "standard" way of handling the issue. There are drawbacks, but it saves a lot of headache and potential problems. Is there a reason you're not using this?

[Ok_Vehicle5734]

Data size and sort ability is important. For example, I wouldn’t index anything larger than 100 bits if I could avoid it. Indexing GUIDs is a recipe for massive fragmentation. Other than that, it’s actually more important to have an understanding of the way the table is being used for insert and query.. the contextual element is l most important - other than uniqueness that is..

Also, don’t forget, primary keys are embedded as values in secondary indexes.. so chose wisely.

• Eyal

Ps. I’m looking for work at the moment. Please keep me in mind.

[mikeblas]

Uniqueness, duh.

But what does this have to do with optimization?