List:General Discussion« Previous MessageNext Message »
From:Jeremy March Date:August 19 2004 10:01pm
Subject:Re: Suggestions? Need a foreign key - but there are tradeoffs
View as plain text  
> I have two tables that should be related by a customer
> id  (established in the customer table).  The second
> table is going to have documents in a "text" column. 
> I want the ability (at least I think) to have full
> text search for this second table.  Hence I chose a
> myIsam table type.  
> So a few things:
> 1-Can I realte the customer ID somehow to the customer
> table.  In my application I'm passing the ID value
> into the record insertion.  By the way , the customer
> table is set currently as Innodb.
> 2- I'm wondering just how bad it would be to not have
> "full text search".  I'm a bit unclear, but I suspect
> that I'd have to format the docs in a certain way to
> highlight the important keywords that would need to be
> searched for.
> 3-If I set both tables to myIsam or leave it as it is,
> is there another way to relate the id's together ?

Yes, the ids are still related in the same way as they would be in an
innodb table--the relationship just isn't _enforced_ by the DBMS.  You
have to enforce the relationship in the application logic.  This is a
very normal situation for everyone who uses myisam tables.  If necessary
you can simulate transactions with LOCK TABLES.  So to insert a row into
the child table which references a row in the parent table you could do
something approximately like this:

1. use lock tables to lock both the parent and child tables--this is so
that no one can delete the valid parent row before you are able to
insert the child row

2. select the desired parent row from the parent table to make sure it
exists

3. insert the child row which references the parent row

4. unlock tables

Do something similar for updates and deletes

Locking the tables like this can slow the application down if you have
many concurrent updates/inserts/deletes, but not necessarily as much as
one might think.  The queries executed between the lock and unlock are
usually very quick so the tables are not locked for very long and
remember that with myisam tables the whole table is locked anyway for
all update/deletes and sometimes for inserts so all you are doing is
locking two tables at the same time instead of just one.  

It all depends on your application and the ratio of
updates/inserts/deletes to selects as to whether this will work for you.

best,
Jeremy March

> Thank you 
> Stuart

Thread
Suggestions?Need a foreign key - but there are tradeoffsStuart Felenstein19 Aug
Re: Suggestions? Need a foreign key - but there are tradeoffsJeremy March20 Aug
  • Re: Suggestions? Need a foreign key - but there are tradeoffsStuart Felenstein20 Aug