By BarisW on

Hi there,

how about the usage of Foreign keys in MySQL?
Today I tried to delete a very large Vocabulary (contained about 20.000 entries), but although I got a 'Succeed' message, about half of the records where still in the database. So I deleted them by hand (delete from term_data where vid = x) but then it came to mind that I forgot the term_synonyms, term_node and the term_hierarchy... Grrrr..

Why isn't Drupal using Foreign keys while this makes live so much easier? Isn't it possible the use them only if the server supports them? And not, if the user is using another kind of database system? What's against the use of it?

Apart from this: is it a problem if I add them manually in the database? (On delete Cascade)

Kind regard,

Baris Wanschers
Sixcolored

⋅ Categories: Drupal 5.x

Comments

escsco’s picture

escsco commented

Last I heard foreign key support wasn't coming until 5.1, unless you were using InnoDB.

Mithrandir’s picture

Mithrandir commented

Well, they might put it in a later version of MySQL for other engines, but for now, you'll need to use InnoDB to have relationships enforced between tables. Although there is some debate on this, the general consensus as I have found it on the web is that InnoDB has some benefits over MyISAM:

- Table locking on a per-row basis instead of per-table basis
- Inserts and updates are many times faster (possibly due to the first benefit)
- Relationships can be enforced

However, the main reason why it is not used much in few-updates-many-selects scenarios (most websites fall into this category) is that MyISAM is generally considered faster than InnoDB on the select statements.

My take is that with a very well indexed table structure, InnoDB can perform well - certainly better than a poorly indexed table structured MyISAM - but that MyISAM simply is the fastest for many selects with only occasional inserts and updates.

I'd be very interested in reading contemporary benchmarks, though.

Anonymous’s picture

Anonymous (not verified) commented

The problem isn't that InnoDB itself is slow, it's that enforcing foreign key relations is slow. I'm a big fan of referential integrity, but in a CMS it's place is in the application layer, not the data layer.

Joshua Boyd
joshua.d.boyd at gmail dot com

turadg’s picture

turadg commented

I know this is an old thread, but maybe somebody else will come across it like I did while trying to find out Drupal's support for foreign keys.

While at this point there's still some controversy about what to do with them,
http://drupal.org/node/111011

the Schema API in D7 does support foreign keys,
http://api.drupal.org/api/group/schemaapi/7

k_zoltan’s picture

k_zoltan commented

Another reply just for the records.

D7 supports foreign keys in the Schema API but only as a documentation feature.

"The information in hook_schema is not used anywhere in Drupal core. It's there so that contrib can take advantage of it.
For Drupal to actually enforce referential integrity, a lot of APIs would have to be adjusted and several parts of core presumably would have to change considerably."

as described by @Frando in the #911352: Document that foreign keys may not be used by all drivers issue.