Category Archives: databases

Foreign keys in MySQL: InnoDB and MyISAM

Posted by on 8 December 2009 15:14 No comments

To create a foreign key in a MySQL database, there are a few things to keep in mind:

  • Any tables using the foreign keys must use the InnoDB engine (MyISAM, the default database engine, doesn’t support them)
  • The foreign key field must be indexed

Jim Epler posted a great tutorial (including screencasts) explaining the process of adding foreign keys.

As far as the differences between InnoDB and MyISAM go, here are my findings:

  • MyISAM is based on a proven, reliable code base
  • MyISAM is very fast and efficient for normal operations, such as selecting and inserting
  • If you need relational design (esp. foreign keys), you’ll need InnoDB
  • InnoDB also supports transactions and row-level locking (as opposed to the table locking of MyISAM
  • InnoDB has better crash recovery, especially on large data sets

See Mike Bernat’s post and this page on INetU. The MySQL developer documentation also provieds a detailed comparison of all the supported database engines.

Oracle 10g ORA_ROWSCN Pseudocolumn

Posted by on 13 October 2009 15:45 4 comments

Oracle 10g maintains a pseudocolumn the tracks the revision number of records. It’s called ORA_ROWSCN. (SCN stands for System Change Number.) There’s also a handy function, SCN_TO_TIMESTAMP, which will convert that revision number to a human-readable date and time.

But there are a few caveats:

  • The revision numbers are tracked on a block level, meaning that even if you modify only one row, surrounding rows may also receive the new version number. You can turn on row-level tracking when you create the table, but there’s no way to do so after the fact.
  • SCN_TO_TIMESTAMP will only be able to convert the SCN to a timestamp if the revision occurred within the last five days. Otherwise you get the lovely error: “ORA-08181: specified number is not a valid system change number”.

This article has a good description and two enlightening examples for those interested.