#1109 - 多重删除中的未知表"QUESTION_ANSWER"

Question

我正在学习MySQL，学习多重删除，但无法完全获得。

我有4张桌子。

1. 用户
2. 问题
3. 答案
4. 问题_答案

我想查询当它删除用户时，它会删除QUESTION、ANSWER、QUESTION_ANSWER中的所有相关数据。

我的表格结构如下

TABLE: ANSWER
ANSWER_NUMBER (PRIMARY KEY)
VOTE_COUNT
isAccepted
ANSWER_DESCRIPTION
USER_ID (FOREIGN KEY FROM USERS.USER_ID)
TABLE: QUESTION
QUESTION_NUMBER (PRIMARY KEY)
QUESTION_DESCRIPTION
VOTE_COUNT
QUESTION_VIEWS
USER_ID
QUESTION_NUMBER_OF_ANSWERS
Table: QUESTION_ANSWER
Q_NUM (FOREIGN KEY FROM QUESTION.QUESTION_NUMBER)
A_NUM (FOREIGN KEY FROM ANSWER.ANSWER_NUMBER)
TABLE USERS:
USER_ID (PRIMARY)
USER_NAME
USER_REPUTATION
USER_NO_OF_ANSWERS
USER_NO_OF_QUESTIONS

假设我想删除用户id 1；我想删除用户id为1的所有问题和答案。

我已经到了这里。

DELETE QUESTION_ANSWER, QUESTION, USERS, ANSWER 
FROM USERS
INNER JOIN
QUESTION ON QUESTION_ANSWER.Q_NUM = QUESTION.QUESTION_NUMBER
INNER JOIN ANSWER
ON ANSWER.ANSWER_NUMBER = QUESTION_ANSWER.A_NUM
WHERE USERS.USER_ID=1;

但它给出错误

DELETE QUESTION_ANSWER, QUESTION, USERS, ANSWER 
FROM USERS
INNER JOIN
QUESTION ON QUESTION_ANSWER.Q_NUM = QUESTION.QUESTION_NUMBER
INNER JOIN ANSWER
ON ANSWER.ANSWER_NUMBER = QUESTION_ANSWER.A_NUM
WHERE USERS.USER_ID=1
MySQL said: Documentation
#1109 - Unknown table 'QUESTION_ANSWER' in MULTI DELETE

Answer 1

参与联接的表都必须在FROM子句之后定义
在DELETE子句中，必须枚举要删除其中行的表

DELETE qa, q, u, a 
FROM USERS u
INNER JOIN QUESTION q ON q.USER_ID = u.USER_ID
INNER JOIN QUESTION_ANSWER qa ON qa.Q_NUM = q.QUESTION_NUMBER
INNER JOIN ANSWER a ON a.ANSWER_NUMBER = qa.A_NUM
WHERE u.USER_ID=1

正如您所看到的，我为这些表使用了别名，因此代码更短，可读性更强

如果您已经用ON DELETE CASCADE定义了表之间的引用完整性，那么类似于：

CREATE TABLE USERS(
USER_ID  int PRIMARY KEY,
USER_NAME VARCHAR(100),
USER_REPUTATION int,
USER_NO_OF_ANSWERS int,
USER_NO_OF_QUESTIONS int
);
CREATE TABLE ANSWER(
ANSWER_NUMBER int PRIMARY KEY,
VOTE_COUNT int,
isAccepted int,
ANSWER_DESCRIPTION VARCHAR(100)
);
CREATE TABLE QUESTION(
QUESTION_NUMBER  int PRIMARY KEY,
QUESTION_DESCRIPTION VARCHAR(100),
VOTE_COUNT int,
QUESTION_VIEWS int,
USER_ID int,
QUESTION_NUMBER_OF_ANSWERS int,
FOREIGN KEY(USER_ID) REFERENCES USERS(USER_ID) ON DELETE CASCADE
);
CREATE TABLE QUESTION_ANSWER(
Q_NUM  int,
A_NUM  int,
FOREIGN KEY(Q_NUM) REFERENCES QUESTION(QUESTION_NUMBER) ON DELETE CASCADE,
FOREIGN KEY(A_NUM) REFERENCES ANSWER(ANSWER_NUMBER) ON DELETE CASCADE
);

那么您所要做的就是从USERS和ANSWER中删除该行，子表的所有行也将被删除：

DELETE u, a 
FROM USERS u
INNER JOIN QUESTION q ON q.USER_ID = u.USER_ID
INNER JOIN QUESTION_ANSWER qa ON qa.Q_NUM = q.QUESTION_NUMBER
INNER JOIN ANSWER a ON a.ANSWER_NUMBER = qa.A_NUM
WHERE u.USER_ID=1

查看简化的演示

Answer 2

首先，当删除任何相关记录时，在您想要删除的外键中添加级联。例如，一个用户可以创建多个问题，这意味着外键在问题表中，因此应用于级联意味着无论何时删除用户，其相关问题也将被删除。

CREATE TABLE USERS(
USER_ID  INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
USER_NAME VARCHAR(100) NOT NULL,
USER_REPUTATION INT NOT NULL,
USER_NO_OF_ANSWERS INT NOT NULL,
USER_NO_OF_QUESTIONS INT NOT NULL
);
CREATE TABLE QUESTION(
QUESTION_NUMBER INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
QUESTION_DESCRIPTION VARCHAR(255) NOT NULL,
VOTE_COUNT INT NOT NULL,
QUESTION_VIEWS INT NOT NULL,
USER_ID INT NOT NULL,
QUESTION_NUMBER_OF_ANSWERS INT NOT NULL,
FOREIGN KEY QUESTION(USER_ID) REFERENCES USERS(USER_ID) ON DELETE CASCADE
);

现在，在两个表中插入一些数据，并尝试删除一个用户，以清楚地理解级联的概念，您将看到，只要删除该用户，与该用户相关的问题也将自动删除。删除查询为：

DELETE FROM USERS [WHERE Clause]

现在你将清楚这个概念，第二步是，尝试在ANSWER表中添加一个外键，并在那里应用级联，因为答案也与用户相关(在你的问题中清楚地表明，你想删除特定用户的答案(。

CREATE TABLE ANSWER(
ANSWER_NUMBER INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
VOTE_COUNT INT,
isAccepted INT,
ANSWER_DESCRIPTION VARCHAR(100) NOT NULL,
USER_ID INT NOT NULL,
FOREING KEY ANSWER(USER_ID) REFERENCES USERS(USER_ID) ON DELETE CASCADE
);

现在最后一件事是从桥接表QUESTION_ANSWER中删除记录。

CREATE TABLE QUESTION_ANSWER(
Q_NUM  INT NOT NULL,
A_NUM  INT NOT NULL,
FOREIGN KEY QUESTION_ANSWER(Q_NUM) REFERENCES QUESTION(QUESTION_NUMBER) ON 
DELETE CASCADE,
FOREIGN KEY QUESTION_ANSWER(A_NUM) REFERENCES ANSWER(ANSWER_NUMBER) ON 
DELETE CASCADE
);

现在你可以去了，只需删除用户的任何记录，它的相关记录在问题，答案&QUESTION_ANSWER表也将被删除。

DELETE FROM USERS [WHERE Clause]