每当我插入tblprowareproducts
时,我希望将外键添加到tblprowareinventory
中:
CREATE TABLE `tblprowareproducts` (
`ItemID` int(11) NOT NULL,
`ItemCode` varchar(30) NOT NULL,
`itemDescription` varchar(60) NOT NULL,
`Strand` varchar(30) NOT NULL,
`UnitCost` double NOT NULL,
`SaleCost` double NOT NULL,
`CategoryID_fk` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
ALTER TABLE `tblprowareproducts`
ADD PRIMARY KEY (`ItemID`),
ADD KEY `CategoryID_fk` (`CategoryID_fk`);
ALTER TABLE `tblprowareproducts`
MODIFY `ItemID` int(11) NOT NULL AUTO_INCREMENT;
ALTER TABLE `tblprowareproducts`
ADD CONSTRAINT `tblprowareproducts_ibfk_1` FOREIGN KEY (`CategoryID_fk`) REFERENCES `tblprowarecategory` (`PCategoryID`) ON DELETE CASCADE ON UPDATE CASCADE;
COMMIT;
tblProwareinventory
CREATE TABLE `tblprowareinventory` (
`inventoryID` int(11) NOT NULL,
`ItemID_FK` int(11) NOT NULL,
`DateOfInventory` date NOT NULL,
`CurrentQuantity` int(11) NOT NULL,
`TotalQuantity` int(11) NOT NULL,
`DeliveredQuantity` int(11) NOT NULL,
`PhysicalCount` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
ALTER TABLE `tblprowareinventory`
ADD PRIMARY KEY (`inventoryID`),
ADD KEY `ItemID_FK` (`ItemID_FK`);
ALTER TABLE `tblprowareinventory`
ADD CONSTRAINT `tblprowareinventory_ibfk_2` FOREIGN KEY (`ItemID_FK`) REFERENCES `tblprowareproducts` (`ItemID`) ON DELETE CASCADE ON UPDATE CASCADE;
COMMIT;
INSERT INTO tblprowareinventory(ItemID_FK)
VALUES ((SELECT ItemID FROM tblprowareproducts))
但是我遇到了这个错误:
![error](https://istack.dev59.com/z2NJh.webp)