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

MySQL used to not honor foreign keys. It still doesn't, unless you take measures.

Out of sight, out of mind, right?

Answer 2

I'm not sure how common it is, but I feel that you should express the conditions of the object model fully regardless of whether the underlying database fully supports them.

If you're generally writing ANSI SQL, then if you go ahead and add the foreign key constraints, then when your database supports them, you use an engine that supports them, or you move to another database that supports them, then you'll get them for "free" and not have to go back and attempt to find all the relations.

So, I would put the foreign keys in SQL anyway, but that's me and again may not be common.

Answer 3

In MySQL only InnoDB even supports Foreign Keys, and only after MySQL 5.1.

Edit: InnoDB will be the default storage engine in MySQL 5.5

Edit-Ignore: Referential Integrety will be a "new feature" in 6.1 according to their roadmap: http://en.wikipedia.org/wiki/MySQL#Future_releases

Answer 4

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.