Dataset - duplicate values when setting primary key

Question

I have a dataset with a column "person_code".

This column contains the following data:

• "Carra"
• "Carra " -> one trailing space

Now if i set the primary key of the dataset to the column "person_code" I'll get the following error:

"These columns don't currently have unique values."

Any way around this? The best I can think of is to add a new column "primary_key" and then replace the ending/starting spaces with another sign. This will cause some extra problems: if I replace them with _ and there's already a Carra_ in the database...

Is there a better way?

Answer 1

This is a database schema question, and the answer to your question is that as you have described it the person_code column is not suitable as a primary key, as its values are not unique.

Your primary key should be 100% unique - usually an incrementally generated number is a suitable choice.

Answer 2

Primary keys are ALWAYS unique, so if two person have the same person code then this will indeed cause this problem

Now, it seems that they are not entirely identical (as you say one has a trailing space. My guess is that your code may remove the trailing space by default. You might want to put a symbol at the start and at the end, like 'Carra' and 'Carra ' to make them different.

You may need to define another symbole that that though. Try one that you know will never be in your data.

Answer 3

Creating a new primary key field, preferably with an int or uniqueidentifier for the data type is the suggested solution for your problem and long term maintenance of your database.

Trailing spaces in a value, in general, are a bad idea. both from a UI perspective, but also regarding data integrity. You may consider cleansing the data, stripping leading and trailing spaces, special chars, etc from your data.