Forum Captain'sLog Crewlist CrewWanted BoatForsale Classifieds Boat Sailor


ALL Boards
  658
News And
Events
  77
Regulations
  18
Racing
  15
Cruising &
Liveaboard
  163
Multihulls
  16
Building &
Repairs
  15
Outfitting
& Gear
  22
7knots FAQ
& General
  51
Development
Bug Reports
  22
Misc & Other
  64
Test Posts
  195
ID: 25 Title: MySQL MyISAM Table Does Not Support Foreign Keys Replis: 0 Read: 1079 Author: 1
Name: Tom Yang  Posts: 110    Vancouver Time: 2003-1-30_3:46:50 Quote    Reply
The main reason I switched database from Informix to MySQL because Informix 7.31 doesn't support TEXT indexing. (Well,Informix 9 does have a state-of-the-art Datablade BLOB indexing, but it costs only half million dollar to buy it. I paid $300 for a single developer license on Linux for informix 7.31 back in 1999).

In current MySQL, there are two major table types: MyISAM and InnoDB. MyISAM has FULLTEXT indexing on TEXT data type but doesn't support Foreign Keys. InnoDB has Foreign Keys and Row Locking... but no BLOB TEXT indexing. The choice is clear to use the ISAM tables otherwise, I might as well stick with Informix. But without Foreign Keys, it's a pain in the neck to maintain the data intergrity.

To describe data integrity in plain language, say, a member signed up at 7k, added his boat and entered several Captain Logs. He brags about how he smuggled drugs in his log reports. Now, he worris that FBI may read his logs and decides he wantd to delete everything on 7knots. He opens his sailor profile form and hits DELETE. So, MySQL takes the order and deletes his entry from the sailor table. However, his boat and logs are left behind without a parent. Children running around in database without a parent is what we call broken data integrity. If the database has foreign keys implemented, when he deletes his data from the sailor table, the built-in table infrastructure could smartly "delete everything on cascade" and removes all its children from the database.

Another situation is when a member sold his boat to another member, he opens his boat form and assigns the boat to the new owner. Unfortunately, he enters the new sailor ID wrong and assigns the boat to #50000, for example. So, MySQL assigns the boat to #50000 without any complaint. Hello, don't you have to check if #50000 exists first before you do that? No, without the foreign key restriction, the boat table just doesn't know about other related tables when altering the sailor_id field to 50000.

Now, I have to manually check all these possibilities. Slow and error prone.