我想你
solId是为了
solitications。我本来以为它是固体（像一块岩石）。因此，这可以通过合成来完成。
请注意，提供的架构中存在一些输入错误。一些逗号缺失，一些索引名称重复。MyISAM表更改为INNODB。所以我做了一些重命名。另外，在table5周围缺少表。因此，就好像您的脚本不会运行（用于table
rfqs）。
类似地，由于提供的表丢失，以下模式将失败，该表大约占整个表的60％到70％。
到目前为止的表：
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 `validitypres` (`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就像一个名称为的TwoParents-
OneChild迷你层次结构
ID。
ID父母的名字也是如此。它有两个父母（
baseRFQId和
SolId）和一个孩子（
NSNId）。名称或标识符as
ID为
baserfqssols它支持的行的FK目标。 引用 和 引用 。
同样，
compTableX002似乎可以解决问题2的条件。
清理：
drop schema slipper;
