mysql - Constraint to prevent violation of FK constraint in a third table

Question

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.

I have several tables, with these key relationships:

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

My questions are:

1. 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?
2. 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, and requires RFQId to reference a BaseRFQId which has the same NSNId as the other two?

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.

EDIT: per @Drew's request, here are the table definitions as they stand now:

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

Answer 1

I figured your solId was for solitications. I originally thought it meant solid (like a rock). So, this can be done through composition.

Note that there were a few typo errors in the schema provided. Some missing commas, some duped up index names. A MyISAM table changed to INNODB. So I did some renaming. Also, around table5 there were missing tables. So it is not as if your script would run (for table rfqs).

Similarly, the following schema will fail due to your missing tables provided, somewhere around 60 to 70 percent through it.

Tables so far:

create schema slipper;
use slipper;

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;

drop table if exists `solicitations`;
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_idx1111` (`NSNId`),
  KEY `NSN_idx1112` (`ID`,`NSNId`), -- atm an necessary evil. Revisit, perhaps collapse one
  CONSTRAINT `NSNId` FOREIGN KEY (`NSNId`) REFERENCES `nsns` (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=latin7;

drop table if exists `parts`;
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_idx2222` (`NSNId`),
  KEY `NSN_idx2223` (`ID`,`NSNId`), -- atm an necessary evil. Revisit, perhaps collapse one
  CONSTRAINT `NSNId2222` FOREIGN KEY (`NSNId`) REFERENCES `nsns` (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=18 DEFAULT CHARSET=latin7;

drop table if exists `baserfqs`;
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_idx4444` (`NSNId`),
  KEY `NSN_idx4445` (`ID`,`NSNId`), -- atm an necessary evil. Revisit, perhaps collapse one
  CONSTRAINT `NSNId4444` 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;


drop table if exists `compTableX001`;
CREATE TABLE `compTableX001`
(  -- a composition table for FK's in `baserfqssols`
  `ID` int(11) AUTO_INCREMENT PRIMARY KEY,
  `BaseRFQId` int(11) NOT NULL,     -- baserfqs.ID
  `SolId` int(11) NOT NULL,         -- solicitations.ID
  `NSNId` int(11) NOT NULL,
  unique key (`BaseRFQId`,`SolId`), -- no dupes allowed  
  CONSTRAINT `tx001_base` FOREIGN KEY (`BaseRFQId`,`NSNId`) REFERENCES `baserfqs` (`ID`,`NSNId`),
  CONSTRAINT `tx001_sol` FOREIGN KEY (`SolId`,`NSNId`) REFERENCES `solicitations` (`ID`,`NSNId`)
) ENGINE=InnoDB DEFAULT CHARSET=latin7; 

drop table if exists `compTableX002`;
CREATE TABLE `compTableX002`
(  -- a composition table for FK's in `rfqssolsparts`
  `ID` int(11) AUTO_INCREMENT PRIMARY KEY,
  `BaseRFQId` int(11) NOT NULL,     -- baserfqs.ID
  `SolId` int(11) NOT NULL,         -- solicitations.ID
  `PartId` int(11) NOT NULL,        -- parts.ID
  `NSNId` int(11) NOT NULL,
  unique key (`BaseRFQId`,`SolId`,`PartId`), -- no dupes allowed 
  CONSTRAINT `tx002_base` FOREIGN KEY (`BaseRFQId`,`NSNId`) REFERENCES `baserfqs` (`ID`,`NSNId`),
  CONSTRAINT `tx002_sol` FOREIGN KEY (`SolId`,`NSNId`) REFERENCES `solicitations` (`ID`,`NSNId`),
  CONSTRAINT `tx002_part` FOREIGN KEY (`PartId`,`NSNId`) REFERENCES `parts` (`ID`,`NSNId`)
) ENGINE=InnoDB DEFAULT CHARSET=latin7; 

drop table if exists `baserfqssols`;
CREATE TABLE `baserfqssols` (
  `ID` int(11) auto_increment,
  `compId` int(11) NOT NULL,    -- composition ID `compTableX001`.`ID`
  `LineItemNo` int(11) NOT NULL DEFAULT '1',
  PRIMARY KEY (`ID`),
  CONSTRAINT `basesol_compX001` FOREIGN KEY (`compId`) REFERENCES `compTableX001` (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin7; -- changed engine type

-- 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, and requires RFQId to reference a BaseRFQId which has the same NSNId as the other two?

drop table if exists `rfqssolsparts`;
CREATE TABLE `rfqssolsparts` (
  -- `RFQId` int(11) NOT NULL,      -- requirement BBBBBBBBBBBBB
  -- `SolId` int(11) NOT NULL,      -- requirement AAAAAAAAA
  -- `PartId` int(11) NOT NULL,     -- requirement AAAAAAAAA
  `ID` int(11) auto_increment,
  `compId` int(11) NOT NULL, -- composition ID `compTableX002`.`ID`
  `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`) 
  PRIMARY KEY (`ID`),
  CONSTRAINT `triplet_compX002` FOREIGN KEY (`compId`) REFERENCES `compTableX002` (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin7;

compTableX001 is like a TwoParents-OneChild mini hierarchy with the name ID. So ID is the name of the parenthood. It has two parents(BaseRFQId and SolId), and one child (NSNId). The name or identifier, as ID, is the FK target of the baserfqssols row that it supports. Referenced and Referencing, respectively.

Similarly, compTableX002 appears to solve the conditions now for Question 2.

cleanup:

drop schema slipper;