我想添加到表中约束有一个小问题:
所以首先在我所有的table上:
DROP TABLE IF EXISTS `Sales`;
CREATE TABLE IF NOT EXISTS `Sales` (
`ItemNo` int(11) NOT NULL,
`Model` varchar(100) NOT NULL unique,
`PurchasingPrice` decimal(11,2) NOT NULL,
`ManufNo` int(11) Not Null,
`LocNo` int(11) NOT NULL,
`SuppNo` int(11) NOT NULL,
`CatNo`int(11) NOT NULL,
`UnitPrice`decimal (11,2),
PRIMARY KEY (`ItemNo`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
INSERT INTO `Sales` (`ItemNo`, `Model`, `PurchasingPrice`,`ManufNo`, `LocNo`,`SuppNo`,`CatNo`,`Unitprice`) VALUES
(1,'Tieflader 18t',6969.84,4,1,6,2,9582.56),
(2,'Betonmischer-3m³',47829.00,3,2,3,3,82457.23),
(3,'Lastenkran 800kg',4129.00,2,2,2,3,8466.43),
(4,'Hubwagen,10m',9478.00,1,2,3,3,18457.84);
DROP TABLE IF EXISTS `Rent`;
CREATE TABLE IF NOT EXISTS `Rent` (
`ItemNo` int(11) NOT NULL, ( Different Number-Area like 1000+, very small project)
`Model` varchar(100) NOT NULL unique,
`PurchasingPrice` decimal(11,2) NOT NULL,
`ManufNo` int(11) Not Null,
`LocNo` int(11) NOT NULL,
`SuppNo` int(11) NOT NULL,
`CatNo`int(11) NOT NULL,
`PricePerDay`decimal (11,2),
`RentDateNo`int(11),
`AdressNo` int(11),
PRIMARY KEY (`ItemNo`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
INSERT INTO `RENT` (`ItemNo`, `Model`, `PurchasingPrice`,`ManufNo`, `LocNo`,`SuppNo`,`CatNo`,`PricePerDay`,`RentDateNo`,`AdressNo`) VALUES
(1000,'Betonmischer 50l',123.45,4,1,6,2,42.56,2,1),
(1001,'Winkelschleifer 800 Watt³',29.00,3,2,3,3,17.23,3,1),
(1002,'Akkuschrauber 12V',129.00,2,2,2,3,16.43,1,2),
(1003,'Akkuschrauber 18V',178.00,1,2,3,3,21.84,1,2);
DROP TABLE IF EXISTS `Stock`;
CREATE TABLE IF NOT EXISTS `Stock` (
`ItemNo` int(11) NOT NULL,
`Min-Stock` int(11) Not Null,
`Current-Stock` int(11) Not Null,
`Max-Stock` int(11) Not Null,
PRIMARY KEY (`ItemNo`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
INSERT INTO `Stock` (`ItemNo`,`Min-Stock`,`Current-Stock`,`Max-Stock`) VALUES
(1000,0,1,1),
(1001,0,1,1),
(1002,0,0,1),
(1003,0,1,1),
(1,2,5,8),
(2,1,2,2),
(3,3,6,9),
(4,1,3,4);
当我尝试添加约束FK时:
ALTER TABLE `Stock`
ADD CONSTRAINT `sales_stock_fk` FOREIGN KEY (`ItemNo`) REFERENCES `sales`(`ItemNo`) ON DELETE RESTRICT ON UPDATE RESTRICT,
ADD CONSTRAINT `rent_stock_fk` FOREIGN KEY (`ItemNo`) REFERENCES `rent`(`ItemNo`) ON DELETE RESTRICT ON UPDATE RESTRICT;
当我删除所有不在销售表中的数字时,我可以使用这个数字:
ALTER TABLE `Stock`
ADD CONSTRAINT `sales_stock_fk` FOREIGN KEY (`ItemNo`) REFERENCES `sales`(`ItemNo`) ON DELETE RESTRICT ON UPDATE RESTRICT;
并且还可以根据需要在销售表中添加新项目和库存中的新项目。但是我不能为租赁项目添加库存,而且同时使用两个表的组合添加约束也不起作用。我不知道如何使它起作用,以便我可以将项目添加到表中。我总是得到#1452子表失败。
也许有人能帮我一个忙。
结果应该是:我进入表库存并将鼠标悬停在一个ItemNo上,当我点击它时,如果ItemNo来自销售,我应该被转发到表销售,如果来自租金,我使用Xampp phpmyadmin,MariaDB
1条答案
按热度按时间2j4z5cfb1#
因此错误是
Cannot add or update a child row: a foreign key constraint fails
,您应该更改ItemNo
上的Sales
表的值,将其在1之前更改为1000或更多您的值为
将其更改为: