Is it a common practice for PHP/MySQL apps to have Foreign Keys declared?

Question

I am working on an open source PHP/MySQL application

I have looked at phpBB, Wordpress, and other apps to see if they specified Foreign Keys or not (to ensure referential integrity)

I cannot find that they have

Is it a common practice in these types of applications to specify Foreign Keys in the MySQL database structure?

Answer 1

Past versions of MySQL use the MyISAM storage engine by default, which does not support foreign key constraints. Unless you explicitly declare tables to use the InnoDB storage engine, or change the default storage engine server-wide, no foreign keys appear, and it's no surprise that software developers who design for MySQL don't bother to use foreign key constraints.

MySQL 5.5 is currently in beta and finally InnoDB will be the default storage engine. So foreign key constraints will be supported out of the box.

Yes, foreign keys are recommended. These constraints help to ensure your data always satisfies referential integrity. The alternative is that your database gradually fills with "crumbs" or rows that refer to a parent row that is no longer in the database. These can lead to strange results from queries and wasted space, inefficient queries, and you end up doing cleanup chores manually that would be unnecessary if you just had the database enforce cleanliness for you.

---

Re comment form @Jacob: Good points, but be sure to read recent articles comparing InnoDB vs. MyISAM Years ago, MyISAM was considered the "fast storage engine" and InnoDB was considered the storage engine you'd reluctantly have to use if you couldn't do without transactions.

But InnoDB has improved dramatically in the past few years, and in most cases today InnoDB performs faster than MyISAM.

Aside from MyISAM still supporting fulltext indexing as you mention, there are fewer and fewer reasons to use MyISAM. When I need fulltext indexing, I either maintain a MyISAM table as a mirror of my primary storage in InnoDB, or else I use Apache Solr.