Re: 5.0 InnoDB problem - ADD CONSTRAINT
Matt Chatterley [EMAIL PROTECTED] wrote: Hi folks. I have a problem creating a foreign key constraint which I just don't seem to be able to figure out. There are three tables, for which the 'show create' output is given below: CREATE TABLE `users` ( `UserID` int(11) unsigned NOT NULL auto_increment, `ContactID` int(10) unsigned NOT NULL default '0', `UserName` varchar(25) NOT NULL default '', `Password` varchar(32) NOT NULL default '', `LoggedOnAt` datetime default '-00-00 00:00:00', `Cookie` varchar(100) NOT NULL default '', PRIMARY KEY (`UserID`), UNIQUE KEY `UserName` (`UserName`), KEY `ContactID` (`ContactID`), KEY `Cookie` (`Cookie`), CONSTRAINT `0_34` FOREIGN KEY (`ContactID`) REFERENCES `contact` (`ContactID`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 CREATE TABLE `usergroups` ( `UserGroupID` int(10) unsigned NOT NULL auto_increment, `Code` varchar(20) NOT NULL default '', `Description` varchar(100) NOT NULL default '', PRIMARY KEY (`UserGroupID`), KEY `CodeLookup` (`Code`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 CREATE TABLE `user_usergroups` ( `UserGroupID` int(11) unsigned NOT NULL default '0', `UserID` int(11) unsigned NOT NULL default '0', PRIMARY KEY (`UserID`,`UserGroupID`), CONSTRAINT `0_75` FOREIGN KEY (`UserID`) REFERENCES `users` (`UserID`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 As you can see, there is one FK on user_usergroups, which references the users table (UserID). However, when I do this, to create a similar constraint on UserGroupID to the usergroups table: ALTER TABLE user_usergroups ADD CONSTRAINT FOREIGN KEY `usergroupid_ref_usergroups` (UserGroupID) REFERENCES usergroups (UserGroupID); I get this error: [Dorfl] ERROR 1005: Can't create table '.\space\#sql-340_b.frm' (errno: 150) The online documentation indicates that this is because my FK constraint is 'badly formed' (I looked up innodb error codes 1005 and 150 in the manual). I have tried recreating the usergroups table with usergroupid as an int(11) (I am unsure as to why it is length 10, rather than 11, to be honest - I created the tables via MySQLCC, and other similar columns are length 11), but this makes no difference. Both columns are unsigned and NOT NULL, and although the documentation states that both parent and child columns must be indexed - they are, because they are both a part (or the whole) of the primary keys. It cannot be because user_usergroups.UserGroupID is part of a combined primary key - because UserID is too! This leaves the only reason I can envisage as: It is because UserGroupID is not the FIRST column referenced in a combined Primary Key - meaning I would have to create a secondary index on it. Is this the case, or have I missed something obvious? Yup. UserGroupID is the secondary column in a multiple-column index. Create separate index on UserGroupID. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
5.0 InnoDB problem - ADD CONSTRAINT
Hi folks. I have a problem creating a foreign key constraint which I just don't seem to be able to figure out. There are three tables, for which the 'show create' output is given below: CREATE TABLE `users` ( `UserID` int(11) unsigned NOT NULL auto_increment, `ContactID` int(10) unsigned NOT NULL default '0', `UserName` varchar(25) NOT NULL default '', `Password` varchar(32) NOT NULL default '', `LoggedOnAt` datetime default '-00-00 00:00:00', `Cookie` varchar(100) NOT NULL default '', PRIMARY KEY (`UserID`), UNIQUE KEY `UserName` (`UserName`), KEY `ContactID` (`ContactID`), KEY `Cookie` (`Cookie`), CONSTRAINT `0_34` FOREIGN KEY (`ContactID`) REFERENCES `contact` (`ContactID`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 CREATE TABLE `usergroups` ( `UserGroupID` int(10) unsigned NOT NULL auto_increment, `Code` varchar(20) NOT NULL default '', `Description` varchar(100) NOT NULL default '', PRIMARY KEY (`UserGroupID`), KEY `CodeLookup` (`Code`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 CREATE TABLE `user_usergroups` ( `UserGroupID` int(11) unsigned NOT NULL default '0', `UserID` int(11) unsigned NOT NULL default '0', PRIMARY KEY (`UserID`,`UserGroupID`), CONSTRAINT `0_75` FOREIGN KEY (`UserID`) REFERENCES `users` (`UserID`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 As you can see, there is one FK on user_usergroups, which references the users table (UserID). However, when I do this, to create a similar constraint on UserGroupID to the usergroups table: ALTER TABLE user_usergroups ADD CONSTRAINT FOREIGN KEY `usergroupid_ref_usergroups` (UserGroupID) REFERENCES usergroups (UserGroupID); I get this error: [Dorfl] ERROR 1005: Can't create table '.\space\#sql-340_b.frm' (errno: 150) The online documentation indicates that this is because my FK constraint is 'badly formed' (I looked up innodb error codes 1005 and 150 in the manual). I have tried recreating the usergroups table with usergroupid as an int(11) (I am unsure as to why it is length 10, rather than 11, to be honest - I created the tables via MySQLCC, and other similar columns are length 11), but this makes no difference. Both columns are unsigned and NOT NULL, and although the documentation states that both parent and child columns must be indexed - they are, because they are both a part (or the whole) of the primary keys. It cannot be because user_usergroups.UserGroupID is part of a combined primary key - because UserID is too! This leaves the only reason I can envisage as: It is because UserGroupID is not the FIRST column referenced in a combined Primary Key - meaning I would have to create a secondary index on it. Is this the case, or have I missed something obvious? Before I forget, I am using (please forgive me), 5.0a-alpha on Windows 2K. Thanks, Matt -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
