"Scott Purcell" <[EMAIL PROTECTED]> wrote on 05/25/2005 09:22:32 AM:
> I can do this programatically, and will alter the table. But there > are three tables that have foreign key references to the table I > will be altering. > > They look like this. > CREATE TABLE ITEM_CAT_REL ( > id INT, > cat_id INT NOT NULL, > key(id), > FOREIGN KEY (id) references ITEM(id) on DELETE CASCADE > ) TYPE=InnoDB; > INSERT INTO ITEM_CAT_REL (id, cat_id) values (5000, 5); > INSERT INTO ITEM_CAT_REL (id, cat_id) values (5000, 6); > > How do I handle these three tables? Do I need to remove the foreign > key somehow? These tables do not need fulltext search, so should I > leave them as an InnoDB, or convert to MyISAM? > > > Thanks, > Scott > > > > > > ### > - ALTER TABLE table_name TYPE=MyISAM; > > - and no, you can't have foreign keys with MyISAM tables--or rather, > there's nothing preventing you using foreign keys but you will have to > enforce referential integrity programmatically. MySQL won't do it for you. > > - ian > > ##### > > http://dev.mysql.com/doc/mysql/en/converting-tables-to-innodb.html > > >key relationships? > > MyISAM can't FK's. > > > Regards, > Rafal > > > > >Thanks, > >Scott > > > >-- One alternative to breaking all of your FK relationships is to split your original table into two pieces. The primary portion stays in InnoDB and keep the FKs with it's child fields. The other half contains just the PK and your text field and is stored as MyISAM. Since you already said that you can ensure relational integrity in code, why not just make sure that when you delete a record from the InnoDB table that you delete it's TEXT field from the MyISAM table at the same time. Same integrity checks but for only two tables (specifically). It will keep your "special handling" overhead to a minimum. Until they add full text indexing to InnoDB, I think this hybrid approach is our only practical solution. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
