On Thu, 13 May 2004 10:34:37 -0700 (PDT) David Blomstrom <[EMAIL PROTECTED]> wrote:
> I thought that only InnoDB tables could be joined - > and only if they had foreign keys. But it sounds like > any kind of table can be joined, and it doesn't need a > foreign key. Exactly, you can do a join with any two or more tables (even the same table joined to itself actually) and on any columns. It's just a matter of whether or not the join makes any sense, for example: SELECT * FROM t1, t2 WHERE t1.city = t2.golfer_id Doesn't make much sense to do this, but the SQL will parse and attempt to execute anyway. > Can someone explain InnoDB, MyISAM and foreign keys in > plain English? If I understand correctly, foreign keys > simply help ensure integrity. To put it another way, > they help weed out errors. InnoDB and MyISAM are table types, or table engines is now the preferred terminology. Different storage implementations basically. MyISAM is so much faster because it doesn't have to deal with the overhead of transactions and foreign keys. An important aspect of foreign keys is the referential action (ON DELETE CASCADE, ON UPDATE SET NULL, etc) which allow you to have the database take care of cascading actions when a parent row is deleted without having to worry about having an application programmer do it and make a coding error or forget to and leave orphaned rows. So if you have a student parent table and an enrollment child table, you can set it to delete rows in the enrollment table for student 123 if student 123 is deleted form the student table (no orphaned rows in enrollment when a student is deleted). You can also set up referential actions to prevent deleting rows from a parent if there are rows in a child table (ON DELETE RESTRICT), it all depends on your situation. Foreign Keys also requires that any rows inserted into the the child table MUST have a value that matches in the parent table. So for the student/enrollment table, if you attempt to insert a row into enrollment for studentId 342, the only way that query will work is if there is indeed a student with studentId 342 in the student table. This is the "referential integrity" part of Foreign Keys. Foreign keys provide a real link between tables to implement an actual relationship between two tables, or even a table to itself. (remember this is a relational database afterall). Without that foreign key, the relationship is only implied and might not be known to anyone unless there is an entity-relationship diagram for the project. This comes in handy when trying to reverse engineer a data model from an existing db application. Data models should always come first though, but we're only human! > For example, when I import data, I often get errors - > something about violations of foreign key restraints. > When I examine my original tables, I often find > discrepancies - like eu* rather than eu in a row for > Turkey, which lies partly in Europe AND Asia. > > I've considered the possibility of creating foreign > keys for quality control, then deleting them after I'm > finished so I can tweak my table - like adding * to > certain elements. However, it sounds like it's very > difficult to delete foreign keys. I tried it in > MySQL-Front or SQLyog and was presented with an > 11-step process, or something like that. ALTER TABLE mytable DROP FOREIGN KEY fk_symbol; (SHOW CREATE TABLE will reveal the fk_symbol) But why even put the foreign key in there in the first place if you're just going to violate it later? If you do that, you should have a "eu*" in the parent table. > My understanding is that MyISAM tables are faster than > InnoDB tables, but the latter offer "row locking," > though I don't really understand what that is. Yes. In addition to foreign keys, InnoDB offers transaction support, which is absolutely critical when dealing with larger OLTP applications. Speed does suffer though because all this Foreign Key / Transaction stuff takes lots of overhead. > Putting it all together, what combination would you > recommend for my project - a series of tables with > data on the world's nations, states, counties and > natural regions? My biggest table at present (Nations) > has about 250 rows and half a dozen columns, but I'll > be creating perhaps a dozen accessory tables, with > data on area, population, economics, government, etc. With rows in the hundreds, InnoDB vs. MyISAM speed should be negligable. Make sure you create Indexes on your foreign key columns and you should be ok. (Indexes will speed up your queries, foreign key columns are almost always the columns used in a join condition, hence the need for an index) > I'm also planning a series of tables focusing on > animals - common and scientific names, diet, habitat, > etc. > > For both of these projects, I think foreign keys would > be a good choice for quality control, which would, in > turn, require the use of InnoDB tables. Am I right? Yes, once you understand how they work and have used them for a little while, you will never want to work without transactions and foreign keys, it just requires too much more error-prone application programming vs. having the database do the work for you. I use InnoDB even for simple two table databases if there is a relationship between the two tables. That's my opinion anyway, Josh -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
