[BioSQL-l] Re: [Open-bio-l] FK cascades

[Thomas Down]

On Wed, Aug 21, 2002 at 11:23:28PM +0100, Matthew Pocock wrote:
> Hi Hilmar, just thinking out loud.
> 
> Presumably MySQL < 3.23.50 will ignore FK constraints if presented with 
> them. So, the same schema deffinition can be used both for both FK-aware 
> (sane) and FK-unaware (not so sane) databases. The differences would be 
> in the adapter layer only. Is this right, or have I missed something? So 
> - one option would be to freeze/branch the current perl biosql adaptor 
> code as-is (doing FK constraints in adaptor code) and develop the trunk 
> assuming a sane RDBMS.

Yes, you can put REFERENCES constraints into your table definitions,
and all vaguely recent versions of MySQL will parse the DDL without
complaints.  If you're just using `restrict' constraints, this is
fine -- you just don't get the safety net when using MySQL.  However,
I think Hilmar is talking about something different, specifically
"on delete cascade" constraints.  This means, for example, that if
I delete one or more rows from the `seqfeature' table, then corresponding
rows from the seqfeature_location table (plus all the other tables
which join onto seqfeature) get automatically deleted too, in the
same transaction.  Makes managing complex data structures far nicer.

But if you're going to rely on this, you really, really, need
all the DBMS you support to implement it properly...

> ps is it just me, or is MySQL the odd one out with all these issues - 
> FK, unique ID generation, transactions, nested joins and so on?

Yes.  Although if you believe the TODO list, by version
4.2 or so it should be quite a nice DBMS.

     Thomas.