Home » RDBMS Server » Performance Tuning » Foreign Keys vs. custom scripts
Foreign Keys vs. custom scripts [message #132855] Tue, 16 August 2005 07:54 Go to next message
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?
Re: Foreign Keys vs. custom scripts [message #132861 is a reply to message #132855] Tue, 16 August 2005 08:03 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10706
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.
Re: Foreign Keys vs. custom scripts [message #133021 is a reply to message #132861] Wed, 17 August 2005 05:19 Go to previous message
Maaher
Messages: 7064
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
Previous Topic: Urgent reply needed
Next Topic: Move Production database to test environment for load tests
Goto Forum:
  


Current Time: Tue Dec 06 20:31:49 CST 2022