Re: Normalization, Natural Keys, Surrogate Keys

Sorry to jump in like this.

I've been reading all the posts here and i keep reading some favoring Natural Keys and some favoring Surrogate Keys.

I also read that some people who were favoring Natural Keys say that Surrogate Keys could be used in some instances. I read a number of posts saying Surrogate Keys should be used appropriately and not all the time.

Me, i've been using only Surrogate Keys and I'm not here to argue whether it's good to use SKs or not since i'm not very experienced in this area.

I have the following situation where i am thinking about switching a couple of tables to using Natural Keys instead of Surrogate Keys.

(SQL Server 7 with Access 2000 Access Project .adp as the Front End)

I have a table for Countries. I have about 240 country names in there. I have :

• CountryNo (Surrogate Key that uses Identity-AutoNumber),
• Country varchar 45
• CountryCode char 2

Should i delete the CountryNo and make the Country the PK of this table? If i do so, that means i will modify all references to CountryNo in other tables
to Country? For example, i will store the Country name and not the CountryNo in my Customers, Suppliers tables?

If I do switch from SK to NK in this example, what do i do when someone renames a country name for example? Do i write a SP that will go to each table where Country exists, update any references to the renamed country?

Ok, i know Country names are not supposed to be renamed often, but for the sake
of argument, let's say the user wants to rename "United States of America" to "USA"
for example? This is a valid reason to rename a country. What do i do in this case
if i have no Surrogate Key. I thought that if i was using a SK, i wouldn't worry
about the country being renamed.

What is your opinion on this?

Another scenario is my States table.

• StateNo (Surrogate Key, Identity)
• State varchar 25
• StateCode char 2
• CountryNo tinyint

I was using StateNo as the PK. I have an Acces form where a user chooses the country from the list of countries and sees all the states for the country
in a subform.
This is where i am confused on whether to switch to NK or stay with SK. If I allow a user to modify/add the states for the country, how do i prevent the user from entering the same State or the same StateCode for the same country? Currently, what i did so far is added two indexes on my tblStates:

• CountryNo and State as Unique Constraint Index
• CountryNo and StateCode as Unique Constraint Index

This seems to be working when i try to add the same state for the same country
or the same state code, i get an error message from SQL Server:

"Violation of UNIQUE KEY constraint 'IX+tblStates'. Cannot insert duplicate key in object 'tblStates'."

"Violation of UNIQUE KEY constraint 'IX+tblStates_1'. Cannot insert duplicate key in object 'tblStates'."

My problem here is that i am unable to intercept this message in my Access form in order to modify the message into a more friendly message to the user.

This started me thinking especially after following this thread for the last couple of weeks. Is it better to use a NK here also:

• Company
• State as a composite PK?

Again, if i do this, how do i address the issue of updating all values of states
in other tables when a state is renamed for example?

Also, if Company+State are the PKs here, how do i address the issue of Company and StateCode should also be unique, meaning I can't allow the user of entering the same State Code more than once for the same country?
Do i create a Unique Constraint Index for this?

Another question I have is : should i think about using Constraints in this case
rather than creating Indexes for Country and StateCode?

I would really appreciate it if someone could tell me if i should switch to NK
for these two tables and simply rely on code and SPs to update all renaming of countries and/or states throughout the database?

My wish is that both people who favor SKs and people who favor NKs would comment here.

Thank you very much Received on Sun May 26 2002 - 22:12:04 CDT