对于级联更新,如果您在数据库中具有外键约束,则根本无法在应用程序空间中执行此 *** 作。
示例:假设您有一个针对美国各州的查找表,主键为两个字母的缩写。然后,您有了一个表,用于引用该表的邮寄地址。有人告诉您,您错误地给Montana缩写了“
MO”而不是“ MT”,因此您需要在查找表中对其进行更改。
CREATE TABLE States (st CHAr(2) PRIMARY KEY, state VARCHAr(20) NOT NULL);INSERT INTO States VALUES ('MO', 'Montana');CREATE TABLE Addresses (addr VARCHAr(20), city VARCHAr(20), st CHAr(2), zip CHAr(6), FOREIGN KEY (st) REFERENCES States(st));INSERT INTO Addresses VALUES ('1301 East Sixth Ave.', 'Helena', 'MO', '59620');
现在,您无需数据库级联更新即可解决错误。以下是使用MySQL 5.0的测试(假设密苏里州没有记录,实际上使用缩写“ MO”)。
UPDATE States SET st = 'MT' WHERe st = 'MO';ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`test/addresses`, ConSTRAINT `addresses_ibfk_1` FOREIGN KEY (`st`) REFERENCES `states` (`st`))UPDATE Addresses SET st = 'MT' WHERe st = 'MO';ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`test/addresses`, ConSTRAINT `addresses_ibfk_1` FOREIGN KEY (`st`) REFERENCES `states` (`st`))UPDATE Addresses JOIN States USING (st)SET Addresses.st = 'MT', States.st = 'MT'WHERe States.st = 'MO';ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`test/addresses`, ConSTRAINT `addresses_ibfk_1` FOREIGN KEY (`st`) REFERENCES `states` (`st`))
没有应用程序侧查询可以解决这种情况。在强制执行引用完整性约束之前,您需要在数据库中级联更新,以便原子地在两个表中执行更新。
欢迎分享,转载请注明来源:内存溢出
评论列表(0条)