SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Enthusiast
    Join Date
    Jun 2004
    Location
    Finland
    Posts
    47
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Foreign key problem

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

    Code:
    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:

    Code:
    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):

    Code:
    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?

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,262
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    in tblComputerSoftwareLink you have defined no index on softwareID

    innoDB requires an index on every column used as a foreign key
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Enthusiast
    Join Date
    Jun 2004
    Location
    Finland
    Posts
    47
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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?

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,262
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Enthusiast
    Join Date
    Jun 2004
    Location
    Finland
    Posts
    47
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ah, you learn something new every day. Thanks!


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •