代码之家 › 专栏 › 技术社区 › DEE

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

phpmyadmin mysql sql

DEE · 技术社区 · 2 年前

以下是我的sql语句:

CREATE DATABASE FOODAPPS;

USE FOODAPPS;

CREATE TABLE Customer (
C_ID        INT             NOT NULL UNIQUE,
C_NAME      VARCHAR(35)     NOT NULL,
C_CON       VARCHAR(20)     NOT NULL,
C_BAL       FLOAT(8,2)      NOT NULL,
PRIMARY KEY(C_ID)
)
ENGINE = INNODB;

CREATE TABLE Restaurant (
R_ID        INT             NOT NULL UNIQUE,
R_ADD       VARCHAR(90)     NOT NULL,
R_CON       VARCHAR(20)     NOT NULL,
R_RATE      DECIMAL(2,1)    NOT NULL,
PRIMARY KEY(R_ID)
)
ENGINE = INNODB;

CREATE TABLE Purchase (
P_ID        INT             NOT NULL UNIQUE,
C_ID        INT,
P_DATE      DATE            NOT NULL,
P_TIME      TIME            NOT NULL,
R_ID        INT,
P_TOTAL     DECIMAL(10,2)   NOT NULL,
PRIMARY KEY(P_ID),
FOREIGN KEY(C_ID)REFERENCES Customer(C_ID),
FOREIGN KEY(R_ID)REFERENCES Restaurant(R_ID)
)
ENGINE = INNODB;

INSERT INTO Customer VALUES('101', 'Devi', '010-8023456', '200.00');
INSERT INTO Customer VALUES('102', 'Jayden', '017-8901234', '650.50');
INSERT INTO Customer VALUES('103', 'Stephanie', '018-9013765', '120.30');
INSERT INTO Customer VALUES('104', 'Michael', '012-3456789', '450.30');
INSERT INTO Customer VALUES('105', 'Abu', '014-2223334', '305.32');

INSERT INTO Restaurant VALUES('201', 'Coconut Street', '088-1234567', '3.5');
INSERT INTO Restaurant VALUES('202', 'Mango Street', '088-2233445', '4.5');
INSERT INTO Restaurant VALUES('203', 'Apple Street', '088-3334445', '4.8');
INSERT INTO Restaurant VALUES('204', 'Peach Street', '088-0110223', '3.7');
INSERT INTO Restaurant VALUES('205', 'Berry Street', '088-8877665', '5.0');

INSERT INTO Purchase VALUES('001', '101', '2024-01-05', '20:08:00', '201', '80.00');
INSERT INTO Purchase VALUES('002', '102', '2024-01-06', '14:08:00', '202', '75.00');
INSERT INTO Purchase VALUES('003', '103', '2024-01-07', '16:05:00', '203', '70.50');
INSERT INTO Purchase VALUES('004', '104', '2024-01-08', '13:25:00', '204', '55.70');
INSERT INTO Purchase VALUES('005', '105', '2024-01-09', '19:05:00', '205', '98.50');

SELECT \* FROM Customer;
SELECT \* FROM Restaurant;
SELECT \* FROM Purchase;

START TRANSACTION;

DELETE FROM Customer WHERE
C_ID = '104';

我试着运行sql语句,所有这些语句都很好,直到我试图从customer表中删除一个customer,它指出了一个错误,上面写着#1451-无法删除或更新父行:外键约束失败( foodapps . purchase ,约束 purchase_ibfk_1 外键( C_ID )参考文献 Customer ( C_ID )).

1 回复 | 直到 2 年前

Andomar 2 年前

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

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

如果删除客户104,则在“采购”中会有一行的C_ID不存在于客户表中。这意味着外键约束不再有效。数据库将强制执行约束并拒绝删除客户

因此,在删除客户104之前,首先从Purchase表中删除相应的行:

DELETE FROM Purchase WHERE C_ID = '104';
DELETE FROM Customers WHERE C_ID = '104';