OraFAQ Forum: Performance Tuning » Foreign Keys vs. custom scripts

Home » RDBMS Server » Performance Tuning » Foreign Keys vs. custom scripts

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Foreign Keys vs. custom scripts [message #132855]

Tue, 16 August 2005 07:54

andogs
Messages: 1
Registered: August 2005
Location: Narvik, Norway

Junior Member

My database/application vendor insists on using scripts/procedures in order of keeping consistance/constraints in table relations, while I mean that foreign keys/relations were made to to this job, and is not affecting performance.

Can anybody give their opinions regarding this? Where can I find documentation?

Report message to a moderator

Re: Foreign Keys vs. custom scripts [message #132861 is a reply to message #132855]

Tue, 16 August 2005 08:03

Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

You are right.
Databases are made for this ( tables level constraints).
Handling constraints/foreign-key in application (procedures/triggers) is the worst recomended method. It adds too much of work for the application.

Report message to a moderator

Re: Foreign Keys vs. custom scripts [message #133021 is a reply to message #132861]

Wed, 17 August 2005 05:19

Maaher
Messages: 7065
Registered: December 2001

Senior Member

I was reading an AskTom thread about it, here's an excerpt:

Tom Kyte on his thread "FK where ever possible"

It is stupid to enforce it via the application AND I'll betcha $5 they aren't
even doing it right (eg: when they delete from the parent, do the LOCK the
entire child table first? Else what is to prevent another session from
inserting a child record that points to this parent that is going to be deleted.
The probably coded something like:

select count(*) from child where fk = :pk;
if count was zero then delete from parent where pk = fk;

During the count, someone is inserting -- you won't see that, they delete -
ORPHAN child)

It is excessively, extremely, very difficult to do RI all by yourself.

But I think Mahesh already pointed this out: "database are made for this". Relational databases that is, of course.

MHE

Report message to a moderator

Previous Topic:	Urgent reply needed
Next Topic:	Move Production database to test environment for load tests

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Apr 10 16:10:20 CDT 2025