mysql - 约束以防止在第三个表中违反 FK 约束

Question

我可以在存储过程中实现我想要的约束，但我想知道我是否可以定义一组外键约束来完成这项工作。

我有几个表，具有以下关键关系:

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

我的问题是:

1. 是否可以在 BaseRFQsSols 上设置外键约束，要求 BaseRFQId 和 SolId 引用具有相同 NSNId 的记录？
2. 是否可以在 RFQsSolsParts 上设置外键约束，要求 SolId 和 PartId 引用具有相同 NSNId 的记录，并且要求 RFQId 引用与另外两个？

我正在使用 MySQL，尽管据我所知(如果我理解错误请纠正我)我所询问的 CONSTRAINT FOREIGN KEY 语法符合 ANSI，因此解决方案不应因 DBMS 实现而异。

编辑:根据@Drew 的要求，这里是现在的表定义:

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

我认为您的 solId 用于征集。我原本以为它的意思是坚固的(像一 block 石头)。因此，这可以通过组合来完成。

请注意，所提供的架构中存在一些拼写错误。一些缺少逗号，一些欺骗了索引名称。 MyISAM 表更改为 INNODB。所以我做了一些重命名。此外，在 table5 周围缺少表格。因此，您的脚本不会运行(对于表 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 `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 就像一个名为 ID 的 TwoParents-OneChild 迷你层次结构。所以 ID 是 parent 身份的名称。它有两个父项(BaseRFQId 和 SolId)和一个子项(NSNId)。名称或标识符，如 ID，是它支持的 baserfqssols 行的 FK 目标。 Referenced 和Referencing，分别。

类似地，compTableX002 现在似乎可以解决问题 2 的条件。

清理:

drop schema slipper;