pros/cons of not using ID's for foreign keys

For a while I have been a strong devote to using unique, autoincrementing ID fields for foreign key relationships.

I have come across some instances where using a UNIQUE code may work as well or even better.  For example:

state
--------
code (PK)
name

city
--------
name
state_code(FK)

where state.code is the states abbreviation (NY = New York).  If I am just hitting the city table I can get the state abbreviation with one table hit.  If I did this with ID's I would always have to join both.  Alternatively, if I need the state.name, I can link via the state.code

So my question is, what are the different philosophies of ID's.  I came across a post that said _never_ link through an ID, since that would limit you ever having disjointed DB servers which periodically sync.

Thoughts, opinions -- thanks
silo77Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
Actually, when in a multi-server environment, you are better off with GUID instead of autoincriment. Although, GUID need more space than Autoincriment integer, they produce values that should be globally unique!

Now, for such small tables as states, where the list won't be that long (and won't change that much), such an overhead isn"t really necessary. Also, a state_code field of NY will be much easier to "read" than a long GUID...
ArjiCommented:
I think the biggest arguement would be that all tables should have PKs regardless.  I know SQL server hates it when you don't have a PK in a table and Access slows down terribly.  Also, using integers as PK/FK is much faster because the server doesn't have to evaluate a text field to something useful in searching.

I've always structured my tables like this but I guess it's a matter of preference:

state
--------
StateID(PK) long integer
code
name

city
--------
CityID(PK) long integer
name
StateID(FK)

Also, this method tends to eliminate problems arising from someone not entering a valid state code.
NatchiketCommented:
Primary keys where the field (or fields) are inherently part of the data as in the situation above are known as Natural Keys or Intelligent Keys.  Keys which are artificial as in autoincrement, GUID etc. are known as surrogate keys.  I expect the debate as to which is better has been raging since the dawn of relational databases.  My own philosophy is if a  Natural Key obviously stands out as being appropriate then use it, otherwise go for a surrogate.
RHansonLineaCommented:
For your specific situation, regarding state codes, I would use the state code.  However, there is a long-standing argument over auto-incrementing vs. natural key.   The use of auto-increment, which I like to call non-sensical, makes it much easier to write joins and to see the direct relationship between related tables as you only have to deal with a single column and it is generally an integer so it is very efficient.  However, I believe it is extremely important to have a natural key defined for ALL tables.  The natural key uniquely identifies each row in the table by virtue of the data in the table - not something that makes no sense.  

When Dr. E.F. Codd defined the rules of normalization in the 1970s, I don't believe that he had intended for keys, or any data, that didn't provide value to the overall data set - such as an auto-incrementing field.  However, as time moves forward, technology changes and newer better ways come to be and I think the auto-increment has provided a valuable way to relate tables easily.  Again, I think that, an auto-increment can work well for most tables as a primary key, but ALL tables should have a natural key that allows you to logically decipher one row from another.  For your state table the state code is a single small size column that actually simplifies the join and provides logical value to the connection that the auto-increment would not - so I would use the state code in your case.

Good luck.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
emilewongCommented:
Pros of using code in the column as primary code:-
1. You can eliminate a ID column in the table which reduce database size.
2. You dont have to use join when you are only want the abbreivation not the full name.
    (e.g) when you retreive data into a dropdown list which only show States Code.
3. Code is more meaningful in SQL statement.
4. Easier to write in Select state using where(Like  'N%') clause. (NY, NJ) not two separate ID "IN [1001, 1004]"
5. Better Performance when using index. because most query retrieve meaningful data.
   Select customerName from Customers where States like 'N%' and the data is stored
   in sequence when using index on States. When using ID, NY and NJ maybe a apart
   from each others.

But using Code as the primary only suitable for small size tables with static data which only have primary key with a single column.

Pros using ID.

1. When using multicolumns primary key (FName , LName). you cannot reference to it with the code.
2. It is hard for you to create manual code for large tables like 200000 products table.
3. Cannot guarantee Unique Code in large tables. ID can be assigned automatically.
4. Easier to identify the Data in term of time. Let say ID 1000-2000 should be very long ago because the last ID is 50000.

There should be more, we can use both Code and ID column for reference in FK to get a better result of database performance.

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Databases

From novice to tech pro — start learning today.