Okay. The purpose of a primary key is to create a unique record. But uniqueness from a database engine perspective is not necessarily the same as uniqueness from a real-world perspecive.
Natural keys perform both these functions.
However, surrogate keys only perform one of them, i.e. identifying records for the database engine to join. It does not, however, help the database user very much to identify unique entities.
For instance, suppose I a customer (Roger J Carlson) to a customer table and the surogate key gets assigned a value of 233. Now, another data entry person also enters the same customer. This copy of Roger J Carlson is assigned a value of 245.
Both Roger J Carlson's refer to the same real-world customer. Which one is right? Impossible to know.
So, while the record is unique from the database engine perspective, it's not from a real-world perspective.
To guard against that, when you use a surrogate key, you also need to create a unique index on a field or combination of fields that will uniquely identify the real-world Roger J Carlson.
Once Roger J Carlson has been entered, it will there after be impossible to enter another Roger J Carlson.
Obviously, in a large database, such a unique key wouldn't work. However, I could use Name, Gender, Birthdate (or some other unique combination of real-world attributes).
A separate field can be added in the table if no existing field produces unique values for every record. MS Access asks the user to create this type of field if the user does not specify primary key while saving the table. MS Access uses the field name ID and data type AutoNumber for this field.
3 comments:
"To protect against that, you should also create a unique index on those fields that would otherwise create a natural key."
Hi Roger! I'm loving going through your very helpful 'What is Normalization' document and I arrived here.
Can you explain that quote a bit more? (I took it from the surrogate key text)
Thanks a ton!
PS: I found my way to your document from the sidebar of a subreddit.. it's highly regarded there (or so I gather)
Okay. The purpose of a primary key is to create a unique record. But uniqueness from a database engine perspective is not necessarily the same as uniqueness from a real-world perspecive.
Natural keys perform both these functions.
However, surrogate keys only perform one of them, i.e. identifying records for the database engine to join. It does not, however, help the database user very much to identify unique entities.
For instance, suppose I a customer (Roger J Carlson) to a customer table and the surogate key gets assigned a value of 233. Now, another data entry person also enters the same customer. This copy of Roger J Carlson is assigned a value of 245.
Both Roger J Carlson's refer to the same real-world customer. Which one is right? Impossible to know.
So, while the record is unique from the database engine perspective, it's not from a real-world perspective.
To guard against that, when you use a surrogate key, you also need to create a unique index on a field or combination of fields that will uniquely identify the real-world Roger J Carlson.
Once Roger J Carlson has been entered, it will there after be impossible to enter another Roger J Carlson.
Obviously, in a large database, such a unique key wouldn't work. However, I could use Name, Gender, Birthdate (or some other unique combination of real-world attributes).
Hope this helps.
A separate field can be added in the table if no existing field produces unique values for every record. MS Access asks the user to create this type of field if the user does not specify primary key while saving the table. MS Access uses the field name ID and data type AutoNumber for this field.
Post a Comment