tags:

views:

19

answers:

2

Hey!

Does anybody have experiences about the mass compatibility (commercial hosts) of relations like

FOREIGN KEY ( `car` ) REFERENCES `vehicles` (`id`) ON DELETE CASCADE ON UPDATE CASCADE

I noticed that other wide spread PHP / MySQL applications do not make use of them while they seem to be very useful.

Any know negative side effects I should know about? Thanks.

+1  A: 

There are no 'compatibility' issues with the foreign keys (or cascade), the rules are simple

  • InnoDB supports it
  • MyISAM does not

You choose which engine you use in your table's DDL.

Unreason
Thanks! However, I still don't understand why (just to give you an example) phpBB is using InnoDB but no foreign keys (or cascades) at all. I guess that foreign keys must have a certain disadvantage I (or we) don't know about and I am afraid that some hosts out there do allow InnoDB while foreign keys are blocked (for performance reasons or whatever).
logtide
@logtide, in short - no. Longer answer would mention a few things, but these are mostly minor (for example if you treat your database as a dumb store, which in case of MyISAM you might _had_ to do, you _do_ have a benefit of being able to replace you persistence layer with something simple and stupid. which, in some cases, might be a benefit)
Unreason
However, in that, you are doing a total opposite of what good system design suggest (which can be ok - there are some applications that are not systems in a 'centralized database system' sense, but still use a database). Normally, in system design you try to keep your integrity rules as close to database as possible; this is good both performance wise and system integrity wise. Of course there is some overhead in this, but scraping integrity for performance most times boils down to removing front wind shield to get a faster car.
Unreason
Thanks again, that really makes sense.
logtide
A: 

They are very useful, and you should use them if you can.

They are only available if you use the InnoDB storage engine, instead of the default MyISAM one. There are some things you should know about InnoDB (the most important being: due to the way it stores the data and indexes tables, you should choose a short primary key). Using InnoDB comes with other benefits: the ability to use transactions, finer control over table locking, and it's less likely to screw up your data when a crash occurs.

For most purposes InnoDB is superior to MyISAM. I would say you should use MyISAM only if

  • you have an acute limit on how much space you can use (InnoDB tables take up a fair bit more space)
  • you require full-text search capabilities (MyISAM is better for this purpose).
Hammerite
Thanks for making this clear.
logtide
Further to your original question... I don't know of any reason why a hosting provider would restrict your use of foreign keys etc. If anything, the use of foreign keys forces you to follow good practices that would decrease the work done by the DBMS, such as properly indexing tables (MySQL will insist that any foreign key columns are indexed, as must be also the columns they reference). My current shared webhost has not objected to my pattern of use of MySQL, and I make use of foreign key constraints.
Hammerite
At any rate, MySQL can be installed without the InnoDB engine, so use of the engine could be disabled if the hosting provider wants.
Hammerite