Enabling Foreign Keys in Moodle

Enabling Foreign Keys in Moodle

by Jeremie S -
Number of replies: 2

Hello,

I'm using:

  • PHP 5.4/5.5
  • Micrsoft IIS 8 server
  • Windows 8 64-bit

I am working with Moodle 1.9.7 and 2.5 databases in PostgreSQL 9.2, and I have noticed that there are no foreign keys between the tables. I can see the indexes having been created, but no foreign keys.

I discovered that in trying to optimize dynamically-generated SQL queries I was making in a Web application that took tremendous amounts of time.

I searched for that on Google and the Moodle forums, and I saw that by default Moodle does not create the foreign keys during installation. However, I read that we can enable foreign keys using this file ("C:\Moodle 2.5\moodle\lib\ddl\sql_generator.php") and/or also the install.xml file in moodle\lib\db.

Would it be possible for you to explain more what it's all about? Do we have to manually create foreign key references on installing Moodle (or using a script or option), or does Moodle include some other mechanism that sort of replaces the foreign keys? I noticed there are indexes, however. Will the absence of foreign keys cause a decrease in performance in the execution of my SQL queries in my PHP Web application?

Any insight will be appreciated.

Thanks, Jeremie

Average of ratings: -
In reply to Jeremie S

Re: Enabling Foreign Keys in Moodle

by Marcus Green -
Picture of Core developers Picture of Particularly helpful Moodlers Picture of Plugin developers Picture of Testers

None of the PHP code depends on foreign key relationships for integrity so you wouldn't gain any  benefit on that count.  For me the only benefit of the fk instructions in the code is that it has allowed me to automatically generate some er diagrams. However the relationships are not complete and some relationships are not relational (or at least they were not in the past). You can try turning them on in sql_generator but I suspect it will throw errors and not complete because some of the relationships don't make sense/are contradictory. If you try it please post the results here.

This was what I was doing when exploring this issue

http://www.examulator.com/er/er_howto.html

Average of ratings: Useful (2)
In reply to Marcus Green

Re: Enabling Foreign Keys in Moodle

by Jeremie S -

Thanks, Marcus, for your quick reply. This is quite helpful to me. 

The only purpose for exploring that option was in case it could improve performance in my Web application, but since it is not the case, then I will not continue exploring that issue.

Thanks again for the insight,

Jeremie

Average of ratings: -