Foreign key problem

[carrot]

Right, so I've got two tables, tblComputer and tblSoftware, setup like so:

CREATE TABLE `tblSoftware` (
  `softwareID` int(10) unsigned NOT NULL auto_increment,
  `programName` varchar(45) NOT NULL default '',
  `purchaseDate` date default '0000-00-00',
  `owner` varchar(45) default NULL,
  `licenseNumber` varchar(100) default '',
  `totalLicenses` int(10) unsigned NOT NULL default '0',
  `usedLicenses` int(10) unsigned NOT NULL default '0',
  `notes` text,
  `createdBy` varchar(45) default NULL,
  `createDate` date default '0000-00-00',
  `lastEditBy` varchar(45) default NULL,
  `lastEditDate` date default '0000-00-00',
  PRIMARY KEY  (`softwareID`)
) TYPE=InnoDB

CREATE TABLE `tblComputer` (
  `ComputerID` varchar(50) NOT NULL default '',
  `serviceDate` date default '0000-00-00',
  `purchaseDate` date default '0000-00-00',
  `processorModel` varchar(50) default NULL,
  `speed` bigint(20) default NULL,
  `ram` int(11) default '0',
  `hdSpace` int(11) default '0',
  `computerType` varchar(50) default NULL,
  `config` varchar(50) default NULL,
  `graphicsCard` varchar(50) default NULL,
  `notes` text,
  `hasOwner` tinyint(1) default '0',
  PRIMARY KEY  (`ComputerID`),
  KEY `tblCOmputerConfigtblComputer` (`config`),
  KEY `tblComputerTypetblComputer` (`computerType`),
  KEY `tblComputerModeltblComputer` (`processorModel`),
  KEY `tblComputerGraphicstblComputer` (`graphicsCard`),
  KEY `tblComputerRAMtblComputer` (`ram`),
  KEY `tblComputerHardDrivetblComputer` (`hdSpace`),
  CONSTRAINT `FK_tblComputer_1` FOREIGN KEY (`config`) REFERENCES `tblComputerConfig` (`computerConfig`) ON UPDATE CASCADE,
  CONSTRAINT `FK_tblComputer_2` FOREIGN KEY (`hdSpace`) REFERENCES `tblComputerHardDrive` (`computerHardDrive`) ON UPDATE CASCADE,
  CONSTRAINT `FK_tblComputer_3` FOREIGN KEY (`processorModel`) REFERENCES `tblComputerModel` (`computerModel`) ON UPDATE CASCADE,
  CONSTRAINT `FK_tblComputer_4` FOREIGN KEY (`ram`) REFERENCES `tblComputerRAM` (`computerRAM`) ON UPDATE CASCADE,
  CONSTRAINT `FK_tblComputer_5` FOREIGN KEY (`computerType`) REFERENCES `tblComputerType` (`computerType`) ON UPDATE CASCADE,
  CONSTRAINT `FK_tblComputer_6` FOREIGN KEY (`graphicsCard`) REFERENCES `tblComputerGraphics` (`computerGraphics`) ON UPDATE CASCADE
) TYPE=InnoDB

I want to create a many-to-many relationship between these two, to keep track of what software is installed on what computer. The syntax for that table is:

CREATE TABLE `tblComputerSoftwareLink` (
  `softwareID` int(10) unsigned NOT NULL default '0',
  `cpuID` varchar(50) NOT NULL default '',
  PRIMARY KEY  (`cpuID`,`softwareID`),
  CONSTRAINT `FK_tblComputerSoftwareLink_1` FOREIGN KEY (`cpuID`) REFERENCES `tblComputer` (`ComputerID`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `FK_tblComputerSoftwareLink_2` FOREIGN KEY (`softwareID`) REFERENCES `tblSoftware` (`softwareID`) ON DELETE CASCADE ON UPDATE CASCADE
) TYPE=InnoDB

This only throws up an Error #1005, and an error 150. It's something to do with the foreign keys, specifically this one (the other FK is created without problems):

CONSTRAINT `FK_tblComputerSoftwareLink_2` FOREIGN KEY (`softwareID`) REFERENCES `tblSoftware` (`softwareID`) ON DELETE CASCADE ON UPDATE CASCADE

I'm completely lost on what the problem is, though. Ideas, anyone?

[r937]

in tblComputerSoftwareLink you have defined no index on softwareID

innoDB requires an index on every column used as a foreign key

[carrot]

That did it, thanks!

There is no index on cpuID either, so how come I could make a foreign key on that column? And isn't the primary key an index, so it should've accepted that?

[r937]

cpuID is leftmost in the primary key index, that's why it can use it

other columns in a compound index require an index scan, that's why it can't use them

[carrot]

Ah, you learn something new every day. Thanks!