Hello,
I have created a table for items. And I needed some of the columns to be
searchable, so I created the table as:
a MyISAM for full text search.
CREATE TABLE `item` (
`parent_id` int(11) NOT NULL default '0',
`id` int(11) NOT NULL auto_increment,
`manufacturer_id` varchar(50) default NULL,,
`limited_qty` char(1) NOT NULL default 'N',
`link_name` varchar(50) default NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `index_id` (`id`),
FULLTEXT KEY `description` (`description`),
FULLTEXT KEY `name` (`name`),
FULLTEXT KEY `name_2` (`name`),
FULLTEXT KEY `name_3` (`name`,`description`)
) TYPE=MyISAM;
The above table item has a primary key of ID. since this is an 'item'
I have 2 other tables that have references to this item:
item_cart_ref and item_category_ref.
I needed to create foreign keys on those two tables, but they are type InnoDB
because of the foreign key.
CREATE TABLE cart_ref_items (
cart_id varchar(50) not null,
item_id int not null,
usertype varchar(20) NOT NULL DEFAULT 'anonymous',
quantity int default 1,
key(item_id),
FOREIGN KEY (item_id) references ITEM(id) on DELETE CASCADE
) TYPE=InnoDB
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
But I cannot mix types, it errors:
So how can one use foreign key references to their main table when it needs to
have full-text search?
Help
Scott
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
