Re: Should I use database foreign keys and indexes

On 11 Jul 2002 06:09:51 -0700, carl.stottlemyer_at_lmco.com (Carl) wrote:
>For example, Lets say you have the table: Employee and use their name
>as the primary key. For the sake of argument lets assume that we
>don't have to worry about duplicate names. We will also have another
>table that defines the organization structure and another table that
>defines salaries and uses the name to reference the employee. If the
>employee name changes then all of the other tables will have to be
>changed also.
...
>In general do you use foreign keys and indexes?

Opinions differ on this.

If EmployeeName is the natural key, and there are several related tables, then the basic approach, still advocated by many people, is to use it as the primary key and foreign key in all relevant places.

Most modern databases have a feature, "cascading updates", such that if you define the cascade as part of the database, if you do have to change the name, the database will do the work of changing it in all of the related places.

On the other hand, many, even most, database designers these days will put the name in one place, and assign it a synthetic, or surrogate key -- likely a big integer. They will then use the integer in all the related tables. Then, if the name changes, you only need to change it in one place.

But that has little to do with flat tables and indices. If you don't normalize the database, odds are you'll start duplicating data and have to update it in many places without being able to use cascading updates.

Hope you have some database folks thereabouts you can run this stuff past. If not, well, I hope you enjoy doing things the hard way.

Joshua Stern Received on Thu Jul 11 2002 - 17:45:21 CEST