phpmyadmin 无法删除或更新父行:外键约束无法删除表

h7appiyu  于 2024-01-09  发布在  PHP
关注(0)|答案(1)|浏览(283)

以下是我的SQL语句:

  1. CREATE DATABASE FOODAPPS;
  2. USE FOODAPPS;
  3. CREATE TABLE Customer (
  4. C_ID INT NOT NULL UNIQUE,
  5. C_NAME VARCHAR(35) NOT NULL,
  6. C_CON VARCHAR(20) NOT NULL,
  7. C_BAL FLOAT(8,2) NOT NULL,
  8. PRIMARY KEY(C_ID)
  9. )
  10. ENGINE = INNODB;
  11. CREATE TABLE Restaurant (
  12. R_ID INT NOT NULL UNIQUE,
  13. R_ADD VARCHAR(90) NOT NULL,
  14. R_CON VARCHAR(20) NOT NULL,
  15. R_RATE DECIMAL(2,1) NOT NULL,
  16. PRIMARY KEY(R_ID)
  17. )
  18. ENGINE = INNODB;
  19. CREATE TABLE Purchase (
  20. P_ID INT NOT NULL UNIQUE,
  21. C_ID INT,
  22. P_DATE DATE NOT NULL,
  23. P_TIME TIME NOT NULL,
  24. R_ID INT,
  25. P_TOTAL DECIMAL(10,2) NOT NULL,
  26. PRIMARY KEY(P_ID),
  27. FOREIGN KEY(C_ID)REFERENCES Customer(C_ID),
  28. FOREIGN KEY(R_ID)REFERENCES Restaurant(R_ID)
  29. )
  30. ENGINE = INNODB;
  31. INSERT INTO Customer VALUES('101', 'Devi', '010-8023456', '200.00');
  32. INSERT INTO Customer VALUES('102', 'Jayden', '017-8901234', '650.50');
  33. INSERT INTO Customer VALUES('103', 'Stephanie', '018-9013765', '120.30');
  34. INSERT INTO Customer VALUES('104', 'Michael', '012-3456789', '450.30');
  35. INSERT INTO Customer VALUES('105', 'Abu', '014-2223334', '305.32');
  36. INSERT INTO Restaurant VALUES('201', 'Coconut Street', '088-1234567', '3.5');
  37. INSERT INTO Restaurant VALUES('202', 'Mango Street', '088-2233445', '4.5');
  38. INSERT INTO Restaurant VALUES('203', 'Apple Street', '088-3334445', '4.8');
  39. INSERT INTO Restaurant VALUES('204', 'Peach Street', '088-0110223', '3.7');
  40. INSERT INTO Restaurant VALUES('205', 'Berry Street', '088-8877665', '5.0');
  41. INSERT INTO Purchase VALUES('001', '101', '2024-01-05', '20:08:00', '201', '80.00');
  42. INSERT INTO Purchase VALUES('002', '102', '2024-01-06', '14:08:00', '202', '75.00');
  43. INSERT INTO Purchase VALUES('003', '103', '2024-01-07', '16:05:00', '203', '70.50');
  44. INSERT INTO Purchase VALUES('004', '104', '2024-01-08', '13:25:00', '204', '55.70');
  45. INSERT INTO Purchase VALUES('005', '105', '2024-01-09', '19:05:00', '205', '98.50');
  46. SELECT \* FROM Customer;
  47. SELECT \* FROM Restaurant;
  48. SELECT \* FROM Purchase;
  49. START TRANSACTION;
  50. DELETE FROM Customer WHERE
  51. C_ID = '104';

字符串
我试着运行SQL语句,所有的都很好,直到我试图从客户表中删除一个客户,它指出了一个错误,说#1451 -无法删除或更新父行:外键约束失败(foodapps. purchase,CONSTRAINT purchase_ibfk_1 FOREIGN KEY(C_ID)REFERENCES CustomerC_ID))。

zvms9eto

zvms9eto1#

在创建Purchase表时,您添加了一个外键。这告诉数据库列C_ID只能具有存在于表Customer列C_ID中的值。

  1. CREATE TABLE Purchase (
  2. ...
  3. FOREIGN KEY(C_ID)REFERENCES Customer(C_ID),

字符串
如果删除客户104,则采购中将有一行的C_ID在客户表中不存在。这意味着外键约束不再有效。数据库将强制执行该约束并拒绝删除该客户
因此,在删除客户104之前,首先从Purchase表中删除相应的行:

  1. DELETE FROM Purchase WHERE C_ID = '104';
  2. DELETE FROM Customers WHERE C_ID = '104';

相关问题