Database scheme includes no foreign keys

[root]

Hello everybody,

while debugging I imported the processwire database into Mysql Workbench and looked at the ERP diagram.

I was wondering that no foreign keys are used anywhere. This can lead to data inconsistency due to any programming error when the PHP scripts are responsible for this.

I can imagine that this might have been the reason for my other problems.

Is there are good reason not to use foreign keys?

Regards,

Fred

[SiNNuT]

Easy answer: PW uses the MyISAM storage engine, which does not support foreign key constraints.

InnoDB could be considered at some point because it has some benefits and also full text search for a while now. But to my knowledge MyISAM still outperforms InnoDB in some areas and there are more things to consider.

[root]

Okay. Thank you.

[ryan]

PW has to support a broad range of MySQL versions. One of PW's distinctions is making all of the data highly accessible, behaving as if it was all in memory  ready to be used, while still being able to scale to millions of pages. One of the important keys to this is fulltext indexes. InnoDB did not support fulltext indexes for a long time, not until recently, in the overall MySQL timeline. While InnoDB now supports fulltext indexes, that's only if your server has a newer MySQL version that supports it, and a great many out there do not support it. Whereas it is universally supported on MyISAM, and has been forever. PW also performs reliably and particularly well with MyISAM (though does with InnoDB too).

Like SiNNuT mentioned above, there are no foreign keys in MyISAM, so it's not even a consideration to be debated. While having foreign keys would have certainly saved time on the development side of PW, and are a nice-to-have feature at the DB level, the lack of them is not going to "lead to data inconsistency" either. We manage our table relations very carefully and thoroughly, as we know we don't have foreign keys managing that for us. To the end user of PW it makes no difference. I'm quite certain that whatever issue you are running into has nothing to do with the lack or presence of foreign keys. 

You can take advantage of InnoDB in PW now if you want to, and we've confirmed it working quite well across several installations. In fact, it's now an installation option available to you. If you click on the gear icon during install (MySQL settings screen) you'll see an experimental option to use InnoDB. While PW won't use features that aren't in both MyISAM and InnoDB (in order to be compatible with both), there are still some benefits to using InnoDB. Specifically, if the power goes out on your server, you won't have a potentially crashed table (needing a repair command). In addition, tables aren't locked during writes, making it work better in high traffic environments that are having to do a lot of INSERT or UPDATE operations in PW (i.e. constant updating or importing pages).