At 16:57 2002-03-13, Rick Flower wrote: >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..
SHOW TABLE STATUS sez it's InnoDB... >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 : OK, which version, exactly, do You get following output from? Oh, never mind, I see it's .47. Which according to Heikki Tuuri of InnoDB is the same as mine... >| 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) Because mine stops after "InnoDB free: <whatever> kB" >>>>>>>>>>>>>>>>>>>>>>>>>>>>>> snip! <<<<<<<<<<<<<<<<<<<<<<<< >Hopefully this might shed some light on your problem.. No, it didn't. It turns out we have (according to InnoDB) the same version of the DB, yet mine behaves differently than mine. Oh, well. Just so You needn't fish out beginning of this thread, mine is 4.0.1. I don't quite know what to think at this point... > 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.. Thanks for the statements below. Unfortunately, after I try them (word for word, I want to eliminate possibility of my error), SHOW TABLE STATUS still ends right after "InnoDB free:<>" comment. >create table if not exists ITEM >( > ITEM_NAME varchar(64) not null, > DESCRIPTION varchar(255), > primary key (ITEM_NAME) >) TYPE=INNODB; >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 Thanks! So, what's next? -------------------------------------------------- sql, query Tomasz Korycki [EMAIL PROTECTED] --------------------------------------------------------------------- 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