Why doesn't Joomla use foreign keys in the core table structures?

Question

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?

Answer 1

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.

Answer 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.