Re: Should I use database foreign keys and indexes

From: Alex Petrov <master.db_at_mail.ru>
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.ORG
Received on Thu Jul 11 2002 - 06:01:26 CEST

Original text of this message