Tomasz writes: >> >From section 16 of http://www.innodb.com/ibman.html you find detailed >>information about every InnoDB version. For example, 4.0.1 == 3.23.47. >> >>Foreign keys should work in 4.0.1.
> Hmmm... That's what I read, too. And after several unsuccesful attempts >to create my own tables, I did those contained on Your site, verbatim (as I >put in my original message). Still, no effect. I guess the question then >becomes: is 4.0.1 really able to keep track of constraints but unable to >show them? In which case, how can one find out what they are (if extant)? Are you sure that you've got a MySQL-Max server, or at least one built with InnoDB support enabled? If you didn't, you might not get an InnoDB table even if you asked for one.. Unfortunately, the SQL parser is somewhat stupid and doesn't bother telling you that you did something dumb or that doesn't make sense in regards to how the server was built.. I've run into things like that numerous times.. As for listing out the foreign key constraints, that only works if you issue a "show table status;" for MySQL 3.23.4x, and you will get something like the following "REFER" statement : | ITEM | InnoDB | Dynamic | 0 | 0 | 16384 | NULL | 0 | 0 | NULL | NULL | NULL | NULL | | InnoDB free: 4901888 kB | | ITEM_DEF | InnoDB | Dynamic | 0 | 0 | 16384 | NULL | 16384 | 0 | NULL | NULL | NULL | NULL | | InnoDB free: 4901888 kB; (ITEM_NAME) REFER dbname/ITEM(ITEM_NAME) mysql> describe ITEM; +-------------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------------+--------------+------+-----+---------+-------+ | ITEM_NAME | varchar(64) | | PRI | | | | DESCRIPTION | varchar(255) | YES | | NULL | | +-------------+--------------+------+-----+---------+-------+ 2 rows in set (0.02 sec) mysql> describe ITEM_DEF; +------------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +------------+-------------+------+-----+---------+-------+ | ITEMDEF_ID | int(11) | | PRI | 0 | | | ITEM_NAME | varchar(64) | YES | MUL | NULL | | +------------+-------------+------+-----+---------+-------+ 2 rows in set (0.00 sec) show create table ITEM; +-------+------------------------------------------------------------------ --------------------------------------------------------------------------- ---------------+ | Table | Create Table | +-------+------------------------------------------------------------------ --------------------------------------------------------------------------- ---------------+ | ITEM | CREATE TABLE `ITEM` ( `ITEM_NAME` varchar(64) NOT NULL default '', `DESCRIPTION` varchar(255) default NULL, PRIMARY KEY (`ITEM_NAME`) ) TYPE=InnoDB | +-------+------------------------------------------------------------------ --------------------------------------------------------------------------- ---------------+ 1 row in set (0.00 sec) mysql> show create table ITEM_DEF; +----------+--------------------------------------------------------------- --------------------------------------------------------------------------- ------------------------------------------------------------+ | Table | Create Table | +----------+--------------------------------------------------------------- --------------------------------------------------------------------------- ------------------------------------------------------------+ | ITEM_DEF | CREATE TABLE `ITEM_DEF` ( `ITEMDEF_ID` int(11) NOT NULL default '0', `ITEM_NAME` varchar(64) default NULL, PRIMARY KEY (`ITEMDEF_ID`), KEY `FK_ITEM_NAME_INDEX` (`ITEM_NAME`) ) TYPE=InnoDB | +----------+--------------------------------------------------------------- --------------------------------------------------------------------------- ------------------------------------------------------------+ 1 row in set (0.00 sec) Hopefully this might shed some light on your problem.. Below are the samples from above that you can feed directly into MySQL and see what it produces.. These work fine on our installation of 3.23.47 -- with InnoDB support enabled of course.. /*==============================================================*/ /* Table: ITEM */ /*==============================================================*/ create table if not exists ITEM ( ITEM_NAME varchar(64) not null, DESCRIPTION varchar(255), primary key (ITEM_NAME) ) TYPE=INNODB; /*==============================================================*/ /* Table: ITEM_DEF */ /*==============================================================*/ create table if not exists ITEM_DEF ( ITEMDEF_ID int not null, ITEM_NAME varchar(64), primary key (ITEMDEF_ID), INDEX FK_ITEM_NAME_INDEX(ITEM_NAME), FOREIGN KEY (ITEM_NAME) REFERENCES ITEM(ITEM_NAME) ) TYPE=INNODB; -- Rick --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
