What kinds of operations are made slow by foreign key checks ? Is it :
- Simple checks on INSERT ?
- Simple checks on UPDATE ?
- Cascaded deletes ?
- Cascaded updates ?
- Locks ?
- Something else ?
Foreign keys are to ensure that the value in a column is always part of a
specific set (the referenced table). If this set changes very rarely, like
the list of countries in the world, or the list of states in a country, or
the various possible states an order can be in (received, processed,
shipped...) then it's alright to skip the check if you're sure your
application will insert a valid value. For some other situations, doing
the check in the application will need some queries and could be slower
(and certainly will be more complicated...)
Are you sure you're not missing a few indexes, which would then force
fkey checks to use sequential scans ?
Thanks for all help!! But my problem is with performance, I agree with
all
of you, the RI must be maintained by the database, because a bunch of
reasons that everyone knows!
But, I'm dealing with a very huge database that servers more than 200
clientes at the same time, and because of it, each manipulation (delete,
insert, update, select) on the database have a poor performance. So, if
we
deal with RI in each client station, we take this work off the database!
The application is an ERP developed with DELPHI + (postgresql or
oracle or
sql server)!!
