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

[Matthew Pocock]

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.

Matthew

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?

Hilmar Lapp wrote:
> Maintaining is FK integrity in the SW layer is not only inevitably 
> unsafe and prone to slip, it also makes for _ugly_ code in the adaptors.
> 
> I don't know exactly what Postgres is capable of, but I'd assume it 
> enforces FK constraints and can do cascading deletes. Oracle does. It 
> seems InnoDB from one of the most recent versions on can do, too.
> 
> How much outcry would it cause to require MySQL-Max > 3.23.50 to run 
> BioSQL, and then migrate to using InnoDB as the table handler? (There 
> are couple problems with the InnoDB implementation of FK constraint 
> enforcement, but if no-one messes with the table definitions and the 
> order they are created, these can be handled I believe.)
> 
>     -hilmar
> 
> (Still cross-posting, but soon I'll post to the biosql-list only. 
> Hopefully I'm not going to be alone there ...)
> 
> -- 
> -------------------------------------------------------------
> Hilmar Lapp                            email: lapp at gnf.org
> GNF, San Diego, Ca. 92121              phone: +1-858-812-1757
> -------------------------------------------------------------
> 
> _______________________________________________
> Open-Bio-l mailing list
> Open-Bio-l@open-bio.org
> http://open-bio.org/mailman/listinfo/open-bio-l
> 


__________________________________________________
Do You Yahoo!?
Everything you'll ever need on one web page
from News and Sport to Email and Music Charts
http://uk.my.yahoo.com