Re: Foreign Keys Constraints, perforamance analysis

> > 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