Optimizer

Optimizer use the information Primary-Key <----> Foreign-Key for optimizing joins? With only index on this columns, I obtain same result ?

Gaetano Mendola© wrote: > > Optimizer use the information Primary-Key <----> Foreign-Key > for optimizing joins? > With only index on this columns, I obtain same result ? Yes. The optimizer does not know about primary and foreign keys only about indexes (the primary and foreign key constraints create supporting indexes if they are not found). The constraints are for documentation and for data integrity. With foreign key and primary keys set up you cannot insert a row into a referencing table if the key value does not exist in the referenced table and similarly you cannot delete a row from a referenced table if any rows in the referencing table refer to its key value. This is known as referential integrity and is the reason for these constraints. Constraints exist for data integrity not optimizer efficiency. Art S. Kagel

I agree that yhe performance will be same whether you implement it using indexes or Primary/Foreign key relationship. WIth With Foreign key it has the addional benefit that it is not going to allow a foreign key to be entered if corresponding primary key is not there. So it is enforcing a business rule in the database in addition to giving you good performance. thanks, khem chander kchande@yahoo.com Gaetano Mendola' <mendola@bigfoot.com> wrote in article <7heofk$aja$1@news.mch.sbs.de>... > Optimizer use the information Primary-Key <----> Foreign-Key > for optimizing joins? > With only index on this columns, I obtain same result ? > > > > > > > >