"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 '0000-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]
