6

I am developing a custom component and want to use a foreign key user_id referenced to #__users(id). I noticed that Joomla doesn't use any foreign key in its core tables for example in #__user_notes or user_profiles.

Assume that I want to create an extension for adding comments to articles. The structure of the table is like so:

CREATE TABLE IF NOT EXISTS `#__comp_comments` (
    `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    `user_id` int(20) unsigned NOT NULL,
    `comment` text,
    PRIMARY KEY (`id`)
    /* PLACEHOLDER */
);

Now I have two options:

, FOREIGN KEY (`user_id`) REFERENCES `#__users`(`id`) ON UPDATE CASCADE ON DELETE CASCADE

and

, KEY `idx_userid` (`user_id`)

What is your suggestion? Using foreign keys in custom components or not? And why?

  • Man not sure what you would expect as foreign key for those tables, but they have the user_id field which connects them with the #__users table. – FFrewin Sep 22 '14 at 17:31
  • @FFrewin, AFAIK when you delete the user, all rows on other tables that connected to the user with foreign keys will be deleted too. For example if Joomla uses the foreign keys, when you delete a user from #__users, all articles belongs to the user, will be deleted too. – Farahmand Sep 22 '14 at 17:41
3

Joomla of course uses foreign keys to reference primary keys in other tables. That's a basic concept in databases and it wouldn't work otherwise.

What you mean is something different. You're talking about binding them together so they keep the integrity on a database level. Eg if a row in the primary table is deleted, the "child" tables gets their rows deleted as well automatically, or you can only delete the primary if all childs are deleted first. That depends a bit on how it's set up. This is called "referential integrity".

In Joomla, we don't use the referential integrity. We instead keep our data sane on an application level. Various plugin events lets you catch a delete or changed state or whatever and act accordingly.

  • 3
    So for "why doesn't Joomla do this at the DB level instead of the application level?", is it because Joomla tries to be more platform agnostic (since it supports MySQL, Postgres, and MSSQL)? – David Fritsch Sep 22 '14 at 19:52
  • 1
    That, and you don't always want it anyway. For example you don't want to delete the articles when you delete their author :) – Bakual Sep 24 '14 at 13:26
  • @Bakual ON DELETE SET NULL... supported by all major databases. – Evan Carroll Mar 30 '17 at 5:39
2

Because only InnoDB storage engine permits foreign key definitions and Joomla can be installed on servers which only support MyISAM tables.

So, for a public component, it's better to avoid foreign keys in table structures.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.