FK and PK Moodle XMLDB

FK and PK Moodle XMLDB

by Luis Sola Ruiz -
Number of replies: 5

Hello everyone! I am trying to represent this entity relationship database diagram thanks to XMLDB tool in Moodle. 



As you can see, it is a generalisation relation, so the children will have the same id that the father (myactivity) but when I try to represent this in XMLDB the select doesn't give the option to define a field as PK and FK to the main table.  First, I define it as primary and then I add a new key but an error appears

The error is : this field is use as a key 




Average of ratings: -
In reply to Luis Sola Ruiz

Re: FK and PK Moodle XMLDB

by Davo Smith -
Picture of Core developers Picture of Particularly helpful Moodlers Picture of Peer reviewers Picture of Plugin developers
In Moodle, the primary key for each table is called 'id', which has an auto-generated value. This is the only primary key allowed.

You are welcome to then add an extra field 'myactivityid' (please use lowercase only), mark it as a foreign key and stick a 'unique' index on it.
Average of ratings: Useful (2)
In reply to Davo Smith

Re: FK and PK Moodle XMLDB

by Luis Sola Ruiz -

Thanks Davo for your answer.

So, in the table myactivity, should I create another field called myactivityid or I can use the primary key to connect the children tables? 

e.g: myactivityid of myactivity_type_quiz table will be fk of id of myactivity table 

Average of ratings: -
In reply to Luis Sola Ruiz

Re: FK and PK Moodle XMLDB

by Davo Smith -
Picture of Core developers Picture of Particularly helpful Moodlers Picture of Peer reviewers Picture of Plugin developers
That sounds right - table myactivity_type_quiz includes myactivityid, which references the id of the myactivity table. This is a pattern used throughout Moodle (e.g. forum_post referencing the id of the forum table; forum referencing the id of the course table). Just one note, before you hit any problems with it, Moodle does not enforce referential integrity in the database, so marking something as a fk simply adds an index to the underlying database. Which is why I suggested also adding a unique index (that doesn't properly solve the referential integrity, but it does prevent duplicates ...)
Average of ratings: Useful (1)