Tomasz, are you sure you are running 4.0.1? In the rpm of 4.0.0 there were no foreign keys.
I tested this on mysql-max-4.0.1, and it worked. Best regards, Heikki Tuuri Innobase Oy --- Order technical MySQL/InnoDB support at https://order.mysql.com/ Speed up adding of features to MySQL/InnoDB through support contracts See http://www.innodb.com for the online manual and latest news on InnoDB ... heikki@hundin:~/mysql-max-4.0.1-alpha-pc-linux-gnu-i686/bin> mysql test Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 to server version: 4.0.1-alpha-max-log Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> CREATE TABLE parent(id INT NOT NULL, PRIMARY KEY (id)) TYPE=INNODB; Query OK, 0 rows affected (0.10 sec) mysql> CREATE TABLE child(id INT, parent_id INT, INDEX par_ind (parent_id), -> FOREIGN KEY (parent_id) REFERENCES parent(id)) TYPE=INN ODB; Query OK, 0 rows affected (0.00 sec) mysql> show table status from test; +--------+--------+------------+------+----------------+-------------+------ ---- -------+--------------+-----------+----------------+-------------+---------- ---+ ------------+----------------+---------------------------------------------- ---- ---------+ | Name | Type | Row_format | Rows | Avg_row_length | Data_length | Max_data_ length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Create_options | Comment | +--------+--------+------------+------+----------------+-------------+------ ---- -------+--------------+-----------+----------------+-------------+---------- ---+ ------------+----------------+---------------------------------------------- ---- ---------+ | child | InnoDB | Fixed | 0 | 0 | 16384 | NULL | 16384 | 0 | NULL | NULL | NULL | NULL | | InnoDB free: 700416 kB; (parent_id) REFER test/pa rent(id) | | parent | InnoDB | Fixed | 0 | 0 | 16384 | NULL | 0 | 0 | NULL | NULL | NULL | NULL | | InnoDB free: 700416 kB | +--------+--------+------------+------+----------------+-------------+------ ---- -------+--------------+-----------+----------------+-------------+---------- ---+ ------------+----------------+---------------------------------------------- ---- ---------+ 2 rows in set (0.03 sec) mysql> -----Original Message----- From: Tomasz Korycki <[EMAIL PROTECTED]> Newsgroups: mailing.database.mysql Date: Thursday, March 14, 2002 6:53 AM Subject: Re: constraints in InnoDB, or is 3.23.43b _really_ < 4.0.1? >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 > --------------------------------------------------------------------- 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
