In my mind, a natural key is like a primary key and thus should identify a unique row in the table.

The specific situation that I'm working on is that a bunch of duplicate data has been inserted into our tables. This is because the tables have no primary keys. I'm now need to know how to systematically identify duplicate records as well as a natural key to create a unique constraint.

Any info is appreciated!

closed as too broad by joanolo, Erik Darling, Andriy M, Marco, Colin 't Hart May 23 '17 at 7:14

Please edit the question to limit it to a specific problem with enough detail to identify an adequate answer. Avoid asking multiple distinct questions at once. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

  • 6
    Wouldn't it be the columns that you're using to decide if data is a duplicate? – Erik Darling May 22 '17 at 19:35
  • 1
    The question, without table definition, and a sample of the data, what's its meaning, and a collection of rows that you consider "duplicate"... cannot really be answered. – joanolo May 22 '17 at 20:10
  • 2
    Natural keys are effectively business rules implemented as integrity constraints in a database. Obviously you need to understand the business rules before you implement them. Analysis of exiting data may assist you but you need to be able to interpret that analysis first and foremost by understanding what real problem you are trying to solve. – nvogel May 23 '17 at 12:35

You are right - a natural key is indeed a combination of one or more columns that uniquely identifies one row in a table.

Natural keys are formed from the business attributes we identify for an entity type. They form part of the vocabulary the users would employ while discussing these entities. They arise "naturally" in discussion: hence the name. Any combination of such attributes that respect the rules of normalisation can form a natural key. Sometimes there can be more than one combination that works. We refer to these as alternate keys. For example, a house could be identified by number, street and town or by latitude and longitude or by postcode and number. There are three different combinations of values that are commonly used by the business, each of which could uniquely identify exactly one house. There are three alternate natural keys.

For your problem you must identify a combination of columns which could uniquely identify a row and distinguish it from the other rows in the table. It will be a combination which will allow you to identify duplicates, as @sp_BlitzErik pointed out. In other words, you can say this row is a duplicate of that row because these columns have the same value in both.

Be careful you choose a combination that must be true because of the definition of the table and the type of data it holds, and not just happens to be true because of the values that happen to be in the table at this particular point in time.

Natural keys stand in contrast to surrogate keys. Surrogate keys are a thing that we invent when implementing a logical data model in physical database management software to account for real-world limitations and peculiarities. Surrogate keys are, as the name suggests, a surrogate for the natural key. They stand in for the natural key and perform its function. Commonly we do this because the natural key is long and contains strings which the DBMS does not process efficiently. The problem is, because we invent them, we can invent values for them and thus enforce uniqueness where the natural key would say there is duplication.

Ideally surrogate keys should only ever be hidden inside our software and never be exposed to the users (though they often are).

Not the answer you're looking for? Browse other questions tagged or ask your own question.