Thanks for your ultra detailed reply. The tables are just samples (no where related to actual table). Well I am not that bad in db designing also ;)
I checked the manual - you indeed can execute two statements like this in one. And if you see the query I am even changing the table type in the later part of the query. Executing it separately causes no problem but I just wanted to know why the two comnined throw up an error. Regards, karam --- Rhino <[EMAIL PROTECTED]> wrote: > There is certainly *something* wrong if the ALTER > TABLE statement causes the > server to hang; the statement should either succeed > or fail with an error > but in neither case should the server hang. > > I'm still back on 4.0.15 so I'm not in a great > position to try the code > myself but I see a few odd things in your > statements. > > First of all, you are using InnoDB for 'master' but > MyISAM for 'child'; as I > recall, MySQL will only enforce foreign keys if both > tables in the > relationship are using InnoDB as their engines. > You'll want to confirm that > in the manual of course, I may just be > misremembering. > > Second, your Alter table statement has a Type=InnoDB > at the end. According > to the manual, you *can* change the type of the > table in an ALTER TABLE > statement. However, based on past experience with > DB2 which works much like > MySQL much of the time, you probably can't do two > changes in the same > statement. You might have better results if you > changed the table type in > one ALTER TABLE and then added the foreign key in > another ALTER TABLE. Or, > drop both tables and recreate them so that both are > InnoDB, then add the > foreign key via ALTER TABLE. > > You can also create the foreign key while you are > creating the 'child' > table; that's how I normally do it. However, you may > be separating the > creation of the 'child' table and the creation of > its foreign key into two > statements deliberately and that should work > correctly. > > By the way, I can't help but notice that your table > design is rather odd. It > makes little sense to have child.id be a foreign key > pointing to master.id > the way you are doing since they will, presumably, > never contain the same > values. After all, child.id contains the child's ID > number while parent.id > contains the parent's ID number which will, > presumably, be different. > Wouldn't it make more sense to do something like > this [untested]? > > create table master > (parent_id int(11) not null, > parent_name char(20) not null, > primary key(parent_id) > ) Engine=InnoDB, charset=utf8; > > create table child > (child_id int(11) not null, > child_name char(20) not null, > parent_id int(11) not null, > primary key(child_id) > foreign key parent_id references master(parent_id) > ) Engine=InnoDB, charset=utf8; > > This would result in tables like this: > > Master > parent_id parent_name > 1 Tom Smith > 2 Mary Jones > > Child > child_id child_name parent_id > 555 Bonnie Smith 1 > 689 Ted Jones 2 > > You could easily look up the names of the parents of > the children by joining > child.parent_id to parent.parent_id and you could be > assured that the > child.parent_id was always a value from the Master > table. > > Rhino > > ----- Original Message ----- > From: "Karam Chand" <[EMAIL PROTECTED]> > To: <mysql@lists.mysql.com> > Sent: Tuesday, January 25, 2005 10:49 AM > Subject: Serious bug (or my foolishness) with alter > table and InnoDB > > > > Hello, > > > > I am running mysql 4.1.7 on Win2K. > > > > I have two tables: > > > > CREATE TABLE `child` ( > > `id` int(11) NOT NULL default '0', > > `name` char(1) NOT NULL default '', > > PRIMARY KEY (`id`,`name`) > > > > > > > > ) ENGINE=MyISAM DEFAULT CHARSET=utf8; > > > > CREATE TABLE `master` ( > > > > > > > > `id` int(11) NOT NULL default '0', > > `name` char(10) NOT NULL default '', > > PRIMARY KEY (`id`,`name`) > > ) ENGINE=InnoDB DEFAULT CHARSET=utf8; > > > > If I execute the following statement: > > > > alter table child add foreign key (id) references > > master (id), type = innodb; > > > > the mysql server hangs and needs to be killed. > After > > restarting the table child is also lost. > > > > Is this a known bug? > > > > Karam > > > > > > > > > > > > > > __________________________________ > > Do you Yahoo!? > > Yahoo! Mail - You care about security. So do we. > > http://promotions.yahoo.com/new_mail > > > > -- > > MySQL General Mailing List > > For list archives: http://lists.mysql.com/mysql > > To unsubscribe: > http://lists.mysql.com/[EMAIL PROTECTED] > > > > > > > > -- > > No virus found in this incoming message. > > Checked by AVG Anti-Virus. > > Version: 7.0.300 / Virus Database: 265.7.2 - > Release Date: 21/01/2005 > > > > > > > > -- > No virus found in this outgoing message. > Checked by AVG Anti-Virus. > Version: 7.0.300 / Virus Database: 265.7.2 - Release > Date: 21/01/2005 > > __________________________________________________ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
