Monday, July 25, 2005

One of the first hurdles I ran into was the fact that MySQL by default does not use referential integrity! It does not use foreign keys and other concepts. This was a big problem for two reasons. Firstly it violates the lazyness rule: I do not want to be responsible to guarantee the integrity of the data. It clutters up your code, and any bugs lead to potentially large amounts of data being wothless. I just want to handle errors where the database tells me I am doing something wrong, leaving the database consistent. Secondly, the MyEclipse Hibernate mapping tool uses keys to build its mapping xml files and generate the Java objects. No keys, no one-to-many and many-to-one types. A way around is to write the mapping files myself, and let Hibernate gernerate the schema, but I really do not want to learn this while there are perfectly decent tools to generate them automatically.

So, for referential integrety to work in MySQL it is critical to use InnoDB tables. A simple howto I found at Referential Integrity in MySQL, lthe MySQL manual is also very useful. I love the way users can enter their own comments. I will probably optimize my server for the InnoDB.