Day: February 25, 2009

Database

Foreign Key constraints in SPDO

While I have long advocated for doing referential integrity in the application space I have also always made use of things like foreign key constraints in PostgreSQL. I know that MySQL has methods for enforcing foreign key constraints in InnoDB tables, but I typically don’t use InnoDB. And until recently I never thought about using SQLite for anything really serious (you can take this to mean I am thinking about using SQLite for something serious). So I began to look into triggers for SQLite to mimic foreign key constraints. This got me thinking about using triggers in MySQL to do the same thing for MyISAM tables. A quick search turned up the answer. I fully expected foreign key constraints in MySQL 6. Of course this thinking comes from the phrase on MySQL documentation for 4.1, 5.0, 5.1, and (sadly) now 6.0:

For storage engines other than InnoDB, MySQL Server parses the FOREIGN KEY syntax in CREATE TABLE statements, but does not use or store it. In the future, the implementation will be extended to store this information in the table specification file so that it may be retrieved by mysqldump and ODBC. At a later stage, foreign key constraints will be implemented for MyISAM tables as well.

Wishful thinking aside, I did get a request to add foreign key constraints to the SPDO implementation, so I think I will get started on adding the following:

  • A SPDOException class to throw back useful information (such as foreign key constraint violation)
  • A method to create tables
  • A method to add foreign key constraints to tables created by SPDO

While I don’t want to get too convoluted with the structure of SPDO, I do want a well-encapsulated and easy-to-use PDO. In that vein the table creation and addition of foreign key constraints may come about via a Table class that provides a few simple methods to do all these things. I tend to determine my data structure before I begin development and build all my tables first, however, if I am careful about how the Table class is constructed I should be able to build all the tables in whatever RDBMS I happen to be using, with the proper triggers and constraints. entirely programmatically.