This issues is this: since Moodle 1.7, the install.xml files that define the structure of the Moodle database have included definitions for foreign keys. That is, role_assignemnt.userid refers to user.id, and so on. This acts as a type of constraint. If you try to instert some data into the role_assignments table with a userid that does not exist in the user table, then the database can check this for you and report an error, and you might end up finding a bug in your code that otherwise you would have missed. That is the theory anyway.
However, Moodle has never enforced these rules in the past (old versions of MySQL did not support foreign keys), so any Moodle system is likely to have some data in the database that does not satisfy the constraints. Therefore, adding the foreign key constraints now would cause things to break, at least until we had done a lot of work to find and fix bugs.
So there is a big practical hurdle in the way of turning on foreign keys and getting the robustness benefits they will bring, and several times in the past groups of Moodle developers have had discussions about whether the long term gain is enough to justify the short term pain. There is not a clear right answer, there are good points on both sides.
That is all a summary of where we are now. My actual reason for posting is to make some specific suggestions:
1. We should make a report so that we can easily find out how close any Moodle site is to obeying all the foreign-key constraints.
In the XMLDB editor, there are already several check options available, like [Check Indexes] [Check Defaults] [Check Bigints]. What these do is to compare the structure of the database you actually have, with what is defined in the install.xml files. So, for example, if your Moodle started life as Moodle 1.5, and has been upgraded several times since then, you can check whether the result of upgrading your old database exactly matches what you would get by doing a fresh install.
I think we should add a new check: [Check foreign keys]. This should be possible because if you have a foreign key like "(userid) references user (id)" on role_assignments, then the query
SELECT count(*)
FROM {role_assignments}
WHERE NOT EXISTS (
SELECT * FROM {user}
WHERE {role_assignments}.userid = {user}.id
)
Will count the number of rows in role_assignments that violate the constrain - and that query can be automatically constructed from the foreign key definition.2. We should create an option for developers, so they can selectively enable certain foreign keys.
The main advantage of foreign keys is they help developers find bugs. Therefore we should make it possible for developers to get that benefit, without causeing extra errors on productions sites. I think this is just like DEVELOPER debug level or XHTML strict headers.
One way to do this would be in the report 1., for every foreign key constraint where there are no rows that currently violate the constraint, have a button to acutally create the foreign key in the database.
That is probably enough for now. If we do thises two things, then we will start to get hard evidence to feed into the debate about whether we should enforce all foreign keys in some future Moodle release.
