FK and PK Moodle XMLDB

FK and PK Moodle XMLDB

- Luis Sola Ruiz の投稿
返信数: 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 




評点平均: -
Luis Sola Ruiz への返信

Re: FK and PK Moodle XMLDB

- Davo Smith の投稿
画像 Core developers 画像 Particularly helpful Moodlers 画像 Peer reviewers 画像 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.
評点平均:Useful (2)
Davo Smith への返信

Re: FK and PK Moodle XMLDB

- 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 

評点平均: -
Luis Sola Ruiz への返信

Re: FK and PK Moodle XMLDB

- Davo Smith の投稿
画像 Core developers 画像 Particularly helpful Moodlers 画像 Peer reviewers 画像 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 ...)
評点平均:Useful (1)