Re: Foreign Keys Constraints, perforamance analysis - Mailing list pgsql-general

> > OK, I've been discussing this with a collegue of mine... and I'm
starting to
> > see the light here...
> >
> > I will, first of all, make a new, simpler, 1<->1 realtionship to test FK
> > constraints... no 2<->1<->2 relasionship here...
>
>     One  thing  you'd  have to take into account is the fact that
>     the MySQL documentation is totally misleading. If you  intend
>     to  compare  apples to apples, you'd have to take a more real
>     world scenario.
>
>     Every business application has to reflect the business  rules
>     it  implements.   Using  a  relational  database  system it's
>     possible to move some of the business logic into the database
>     itself.  So  we  allways have to look at the middleware (e.g.
>     PHP) and the database (e.g. Postgres)  as  a  unit.   If  the
>     business modell now requires some referential integrity, like
>     there can never be  an  invoice  referencing  a  non-existent
>     customer,  this  unit has to ensure it, no matter what. There
>     are two possible solutions,
>
>     1.  Setup a foreign key constraint, so that the database will
>         not allow the insertion of the invoice or deletion of the
>         customer, however concurrent the DB access might be.
>
>     2.  Implement the required checks  with  appropriate  locking
>         and   transaction   coverage   at   every  place  in  the
>         application, where these two relations are  modified  WRT
>         the logical requirement of the business model.
>
>     IMHO  solution #1 has a major advantage. Only the DB designer
>     really must understand the entire business modell  to  ensure
>     that  there  will never be logically inconsistent data in the
>     database. The worst thing that can happen if a WEB  developer
>     doesn't  honor  the business modell is an aborted transaction
>     and maybe an error message the user doesn't  understand.  For
>     #2  every  WEB  programmer,  at any time editing a single PHP
>     code snippet, has to have the business modell in  mind.  Here
>     the  worst  possible consequence is violation of the business
>     modell. So we might have invoices where we don't know anymore
>     who's the customer.
>
>     Thus,   to   compare   MySQL  vs.  Postgres  WRT  referential
>     integrity, create a sample  application  where  the  Postgres
>     version  checks  for  possible errors (can be optimized using
>     deferred constraints and checking just at  the  COMMIT).  The
>     MySQL  version  instead  implements  the  constraints  on the
>     middleware level including transaction handling and  locking,
>     so you'd have to use BDB tables only for example.
>
>     Just my $0.02
>
>
> Jan
>
> --

Thanks for the input!
... anyway, I never had in mind to compare MySQL to PostgreSQL... just to
see what performance impact the little check the "foreign key references"
has on an insert... and what performance impact ON DELETE CASCADE has when
deleting (if you choose to let the dbmanager to handle it)...

Very good points, and well explained!

Daniel Åkerud