> 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.
The ability to join a bunch of tables in a query is different from foreign keys. A foreign key is a relationhip between two tables. It says that if you wish to put a value into a column with a foreign key, then that value has to exist in the other table. > 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 is a storage engine, and so is MyISAM. When you create a table, you specify one of the types. When you add data and indexes to that table, the type of table determins the storage engine used. InnoDB has row level locking. This means that when you are updating a row, only that one row gets locked (which means that another connection to the database cannot modify that row). MyISAM locks the entire table. Only one connection/session at a time can update/insert/delete. InnoDB uses the concept of a tablespace; MyISAM doesn't. A tablespace is where you store your data, and is made up of datafiles. You don't know where your data is stored in those data files. When you create a table in MyISAM, it creates a file of the same name as your table. InnoDB has foreign keys. What you wrote above is correct - you're defining a relationship between tables that the database will enforce. To backup InnoDB, you either have to shut down the database, or buy a hot-backup tool ($500 US, 390 Euros, I think). There is lots more, but those are the basics. MyISAM is easier, InnoDB has more enterprise features. > 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. Yes, the keys have to be an exact match. You could also allow a country to be in two continents with a one-to-many relationship. > 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. It's not that tough. ALTER TABLE your_table_here ADD CONSTRAINT some_foreign_key_name FOREIGN KEY (column from table) REFERENCES other_table (other column)l ALTER TABLE your_table_here DROP FOREIGN KEY some_foreign_key_name; Note that it depends on the version of MySQL you are using. Some versions of MySQL would not allow you to create foreign keys outside of CREATE-TABLE statements. Others wouldn't allow you to give your foreign key a name. Checkout http://www.innodb.com/ibman.php - it's the InnoDB manual. > 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. Explained it above. > 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. > > 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, foreign keys would help catch bugs. If there is alot of updating/inserting/deleting, InnoDB can be very helpful as well. Davis -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
