title: Constraint to prevent violation of FK constraint in a third table
body: <p>I can implement the constraints that I want in stored procs, but I'm wondering if I can define a set of foreign key constraints which will do the job.</p>
  
  <p>I have several tables, with these key relationships:</p>
  
  <pre><code>NSNs
  ---
  Id  PK
  
  Solicitations
  ----
  Id            PK
  NSNId         FK - NSNs
  
  Parts
  -----
  Id        PK
  NSNId     FK - NSNs
  
  BaseRFQs
  -------
  Id      PK
  NSNId   FK - NSNs
  
  RFQs
  ----
  Id          PK
  BaseRFQId   FK - BaseRFQs
  
  BaseRFQsSols
  ------------
  BaseRFQId PK/FK - BaseRFQs
  SolId     PK/FK - Solicitations
  
  RFQsSolsParts
  -------------
  RFQId     PK/FK - RFQs
  SolId     PK/FK - Solicitations
  PartId    PK/FK - Parts
  </code></pre>
  
  <p>My questions are:</p>
  
  <ol>
  <li>Is it possible to set up a foreign key constraint on BaseRFQsSols that requires both the BaseRFQId and the SolId to reference records that have the same NSNId?</li>
  <li>Is it possible to set up a foreign key constraint on RFQsSolsParts that requires SolId and PartId to reference records that have the same NSNId, <em>and</em> requires RFQId to reference a BaseRFQId which has the same NSNId as the other two?</li>
  </ol>
  
  <p>I'm using MySQL, although as I understand it (please correct me if I understand wrong) the CONSTRAINT FOREIGN KEY syntax I'm asking about is ANSI-compliant, so the solution oughtn't to vary between DBMS implementations.</p>
  
  <p>EDIT: per @Drew's request, here are the table definitions as they stand now:</p>
  
  <pre><code>CREATE TABLE `nsns` (
    `ID` int(11) NOT NULL AUTO_INCREMENT,
    `NSN` char(16) NOT NULL,
    `Description` varchar(100) DEFAULT NULL,
    `ShortDesc` varchar(20) DEFAULT NULL,
    PRIMARY KEY (`ID`),
    UNIQUE KEY `NSN_UNIQUE` (`NSN`)
  ) ENGINE=InnoDB AUTO_INCREMENT=42 DEFAULT CHARSET=latin7
  
  CREATE TABLE `solicitations` (
    `ID` int(11) NOT NULL AUTO_INCREMENT,
    `NSNId` int(11) NOT NULL,
    `UOMId` int(11) NOT NULL DEFAULT '1',
    `QUPId` int(11) NOT NULL DEFAULT '0',
    `SolicitationNo` char(16) NOT NULL,
    `Quantity` int(11) NOT NULL,
    `ReturnByDate` date NOT NULL,
    `StatusId` int(11) NOT NULL DEFAULT '1',
    `Memo` text,
    PRIMARY KEY (`ID`),
    UNIQUE KEY `SolicitationNo_UNIQUE` (`SolicitationNo`),
    KEY `NSN_idx` (`NSNId`)
    CONSTRAINT `NSNId` FOREIGN KEY (`NSNId`) REFERENCES `nsns` (`ID`)
  ) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=latin7
  
  CREATE TABLE `parts` (
    `ID` int(11) NOT NULL AUTO_INCREMENT,
    `NSNId` int(11) NOT NULL,
    `VendorId` int(11) NOT NULL,
    `UOMId` int(11) NOT NULL DEFAULT '1',
    `QUPId` int(11) NOT NULL DEFAULT '1',
    `StatusId` int(11) DEFAULT '1',
    `PartNo` varchar(45) DEFAULT NULL,
    `Memo` text,
    PRIMARY KEY (`ID`)
    KEY `NSN_idx` (`NSNId`)
    CONSTRAINT `NSNId` FOREIGN KEY (`NSNId`) REFERENCES `nsns` (`ID`)
  ) ENGINE=InnoDB AUTO_INCREMENT=18 DEFAULT CHARSET=latin7
  
  CREATE TABLE `baserfqs` (
    `ID` int(11) NOT NULL AUTO_INCREMENT,
    `NSNId` int(11) NOT NULL,
    `BRFQNo` varchar(45) DEFAULT NULL,
    `Memo` text,
    `Finalized` bit(1) NOT NULL DEFAULT b'0',
    PRIMARY KEY (`ID`),
    UNIQUE KEY `BRFQNo_UNIQUE` (`BRFQNo`),
    KEY `NSN_idx` (`NSNId`),
    CONSTRAINT `NSNId` FOREIGN KEY (`NSNId`) REFERENCES `nsns` (`ID`)
  ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin7
  
  CREATE TABLE `rfqs` (
    `ID` int(11) NOT NULL AUTO_INCREMENT,
    `BaseRFQId` int(11) NOT NULL,
    `VendorId` int(11) NOT NULL,
    `RFQNo` varchar(45) NOT NULL,
    `StatusId` int(11) NOT NULL DEFAULT '6',
    `DateSent` date DEFAULT NULL,
    `DateResponded` date DEFAULT NULL,
    `VendorNotes` text,
    `QuotedBy` varchar(45) DEFAULT NULL,
    `Title` varchar(45) DEFAULT NULL,
    `ValidityCodeId` int(11) DEFAULT '4',
    `UnitWt` decimal(10,3) DEFAULT NULL,
    `WtUOMId` int(11) DEFAULT '1',
    PRIMARY KEY (`ID`),
    UNIQUE KEY `RFQNo_UNIQUE` (`RFQNo`),
    KEY `BaseRFQId_idx` (`BaseRFQId`),
    KEY `VendorId_idx` (`VendorId`),
    KEY `StatusId_idx` (`StatusId`),
    KEY `ValidityCodeId_idx` (`ValidityCodeId`),
    KEY `WtUOMId_idx` (`WtUOMId`),
    CONSTRAINT `WtUOMId` FOREIGN KEY (`WtUOMId`) REFERENCES `wtuoms` (`ID`),
    CONSTRAINT `BaseRFQId` FOREIGN KEY (`BaseRFQId`) REFERENCES `baserfqs` (`ID`),
    CONSTRAINT `StatusId` FOREIGN KEY (`StatusId`) REFERENCES `rfqstatus` (`ID`),
    CONSTRAINT `ValidityCodeId` FOREIGN KEY (`ValidityCodeId`) REFERENCES `validitycodes` (`ID`),
    CONSTRAINT `VendorId` FOREIGN KEY (`VendorId`) REFERENCES `vendors` (`ID`)
  ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin7
  
  CREATE TABLE `baserfqssols` (
    `BaseRFQId` int(11) NOT NULL,
    `SolId` int(11) NOT NULL,
    `LineItemNo` int(11) NOT NULL DEFAULT '1',
    PRIMARY KEY (`BaseRFQId`,`SolId`),
    KEY `RFQ_idx` (`BaseRFQId`),
    KEY `Solicitation_idx` (`SolId`)
  ) ENGINE=MyISAM DEFAULT CHARSET=latin7
  
  CREATE TABLE `rfqssolsparts` (
    `RFQId` int(11) NOT NULL,
    `SolId` int(11) NOT NULL,
    `PartId` int(11) NOT NULL,
    `CondId` int(11) NOT NULL,
    `UOMId` int(11) NOT NULL DEFAULT '1',
    `QUPId` int(11) NOT NULL DEFAULT '1',
    `UnitPrice` decimal(10,3) NOT NULL,
    `LeadTime` int(11) DEFAULT NULL,
    `LTCId` int(11) DEFAULT NULL,
    `SplsNSNId` int(11) DEFAULT NULL,
    `SetupCostInc` bit(1) NOT NULL DEFAULT b'0',
    `CertCostInc` bit(1) NOT NULL DEFAULT b'0',
    `MfgCerts` bit(1) NOT NULL DEFAULT b'0',
    `Altered` bit(1) NOT NULL DEFAULT b'0',
    `OrigPkg` bit(1) NOT NULL DEFAULT b'1',
    `SplsContNo` varchar(45) DEFAULT NULL,
    `SplsDate` date DEFAULT NULL,
    PRIMARY KEY (`RFQId`,`SolId`,`PartId`)
  ) ENGINE=InnoDB DEFAULT CHARSET=latin7
  </code></pre>