Interesting problem with Alter table and foreign keys on 3.23.51

2002-06-18 Thread Carl McNamee 

Below is an example of a problem I'm having when issuing an alter table
command to create a foreign key in mysql version 3.23.51.  I am running the
max version and the tables exist in the innodb table space.

Thoughts?  Comments?  Criticism?

Carl McNamee
Systems Administrator
Billing Concepts
(210) 949-7282


mysql alter table Table2 add constraint foreign key (par_id) references 
Table1 (id);
ERROR 1005: Can't create table './test/#sql-6b2e_f.frm' (errno: 150)
mysql show create table Table1\G
*** 1. row ***
   Table: Table1
Create Table: CREATE TABLE `Table1` (
  `id` int(11) NOT NULL default '0',
  PRIMARY KEY  (`id`)
) TYPE=InnoDB
1 row in set (0.00 sec)

mysql show create table Table2\G
*** 1. row ***
   Table: Table2
Create Table: CREATE TABLE `Table2` (
  `name` char(10) NOT NULL default '',
  `par_id` int(11) NOT NULL default '0',
  PRIMARY KEY  (`name`)
) TYPE=InnoDB
1 row in set (0.00 sec)

mysql

-
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

Re: Interesting problem with Alter table and foreign keys on 3.23.51

2002-06-18 Thread Victoria Reznichenko 

Carl,
Tuesday, June 18, 2002, 5:30:31 PM, you wrote:

CM Below is an example of a problem I'm having when issuing an alter table
CM command to create a foreign key in mysql version 3.23.51.  I am running the
CM max version and the tables exist in the innodb table space.

CM Thoughts?  Comments?  Criticism?

par_id column in the Table2 must be indexed.

CM Carl McNamee
CM Systems Administrator
CM Billing Concepts
CM (210) 949-7282

[skip]

CM Create Table: CREATE TABLE `Table2` (
CM   `name` char(10) NOT NULL default '',
CM   `par_id` int(11) NOT NULL default '0',
CM   PRIMARY KEY  (`name`)
CM ) TYPE=InnoDB
CM 1 row in set (0.00 sec)




-- 
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.net http://www.ensita.net/
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /Victoria Reznichenko
 / /|_/ / // /\ \/ /_/ / /__   [EMAIL PROTECTED]
/_/  /_/\_, /___/\___\_\___/   MySQL AB / Ensita.net
   ___/   www.mysql.com




-
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