11 messages in com.mysql.lists.mysqlRe: constraints in InnoDB, or is 3.23...| From | Sent On | Attachments |
|---|---|---|
| Tomasz Korycki | 12 Mar 2002 15:03 | |
| Sanjay Chigurupati | 12 Mar 2002 18:49 | |
| Heikki Tuuri | 13 Mar 2002 05:13 | |
| Tomasz Korycki | 13 Mar 2002 13:13 | |
| Rick Flower | 13 Mar 2002 13:56 | |
| Tomasz Korycki | 13 Mar 2002 20:51 | |
| Heikki Tuuri | 13 Mar 2002 22:57 | |
| Heikki Tuuri | 14 Mar 2002 04:22 | |
| Tomasz Korycki | 14 Mar 2002 07:52 | |
| Tomasz Korycki | 14 Mar 2002 08:03 | |
| Heikki Tuuri | 14 Mar 2002 08:22 |
| Subject: | Re: constraints in InnoDB, or is 3.23.43b _really_ < 4.0.1? |
|---|---|
| From: | Heikki Tuuri (Heik...@innodb.com) |
| Date: | 03/13/2002 10:57:12 PM |
| List: | com.mysql.lists.mysql |
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 <engi...@flow.mine.nu> 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 engi...@flow.mine.nu
--------------------------------------------------------------------- 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 <mysq...@lists.mysql.com> To unsubscribe, e-mail <mysql-unsubscribe-mysql=free...@lists.mysql.com> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php