Re: Should I use database foreign keys and indexes
Date: Thu, 11 Jul 2002 04:01:26 +0000 (UTC)
Message-ID: <ce4f0fc2d221b8c9a41ebc04a957ac1a.54296_at_mygate.mailgate.org>
Hi,
>I recently had a discussion with several web application developers
>where I work about the use of foreign keys and table indexes.
>...I was suprised that they felt that foreign keys and indexes
>add unnecessary complexity to a database and that they never use
>foreign key constraints and rarely use indexes as a primary key.
It's not the problem area that should be reffered to Web developers (or you should call them some other way, say Database developers/analysts). Web developers have to write application/middle-tier code/scripts. (It's mostly style of low-cost/weak-design/cheap-simple software companies that employ -all in one- people.)
>Foreign Keys Constraints:
> Pro:
> Provide referential integrity
Ok.
> Con:
> May have to disable when you are doing mass updates
>
In fact, depends on the DBMS you use.
* Slows down INSERTs. (It's obvious because DBMS requires some time to
validate any of such an insert operation.)
>Table Indexes as Primary Key:
> Pro:
> Unique reference to the table so that a change to a datafield
>doesn't require a change to all tables that reference it
(Something strange you are about. Would you explain the above one?) * In general, indexes are intended to speed up queries (mostly SELECT, but also UPDATE and INSERT queries).
> Con:
> May have to do a table join to search on a particular table field
Indexes should be rebuilt to maintain their accuracy (sometimes this should be done in real time manner --slows down the performance).
> CONCLUSION:
Yes, you should if you really need them (PK/FK constraints and Indexes).
Good luck
Alex
System Analyst/Architect, DB Progarammer mcdba,ocp
-- Posted via Mailgate.ORG Server - http://www.Mailgate.ORGReceived on Thu Jul 11 2002 - 06:01:26 CEST