问题描述
我不是MySQL专业人员,我只是想制作一个简单的Java应用程序,该应用程序可以访问MySQL数据库(例如著名的图书/作者数据库)并进行操作(即添加,更新,删除).
问题是,当我尝试使用
删除时,有两个表具有多对多关系(例如authors表和books表)DELETE FROM tableName WHERE someColumnName = 'someValue'
它会引发MySQLIntegrityConstraintViolationException,并显示消息无法删除或更新父行:外键约束失败"
我知道这是由于多对多"的关系,但是实际上我不知道该怎么办,因为我不是MySQL专业人员.任何帮助将不胜感激.谢谢.
修改:这是数据库
DROP DATABASE IF EXISTS books;
CREATE DATABASE books;
USE books;
CREATE TABLE Authors
(
AuthorID int NOT NULL AUTO_INCREMENT PRIMARY KEY,
FirstName varchar(30) NOT NULL,
LastName varchar(30) NOT NULL
) ;
CREATE TABLE Titles
(
ISBN varchar(20) NOT NULL PRIMARY KEY,
Title varchar(100) NOT NULL,
EditionNumber int NOT NULL,
Copyright varchar(4) NOT NULL
) ;
CREATE TABLE AuthorISBN
(
AuthorID int NOT NULL,
ISBN varchar(20) NOT NULL,
FOREIGN KEY (AuthorID) REFERENCES Authors(AuthorID) ON DELETE SET NULL,
FOREIGN KEY (ISBN) References Titles(ISBN) ON DELETE SET NULL
) ;
INSERT INTO Authors (FirstName,LastName) VALUES ('Paul','Deitel') ;
INSERT INTO Authors (FirstName,LastName) VALUES ('Harvey','Deitel') ;
INSERT INTO Authors (FirstName,LastName) VALUES ('Abbey','Deitel') ;
INSERT INTO Authors (FirstName,LastName) VALUES ('Michael','Morgano') ;
INSERT INTO Authors (FirstName,LastName) VALUES ('Eric','Kern') ;
INSERT INTO Titles (ISBN,Title,EditionNumber,Copyright) VALUES ('0132152134','Visual Basic 2010 How to Program',5,'2011') ;
INSERT INTO AuthorISBN (AuthorID,ISBN) VALUES (1,'0132152134') ;
INSERT INTO AuthorISBN (AuthorID,ISBN) VALUES (2,'0132152134') ;
INSERT INTO Titles (ISBN,Title,EditionNumber,Copyright) VALUES ('0132151421','Visual C# 2010 How to Program',4,'2011') ;
INSERT INTO AuthorISBN (AuthorID,ISBN) VALUES (1,'0132151421') ;
INSERT INTO AuthorISBN (AuthorID,ISBN) VALUES (2,'0132151421') ;
INSERT INTO Titles (ISBN,Title,EditionNumber,Copyright) VALUES ('0132575663','Java How to Program',9,'2012') ;
INSERT INTO AuthorISBN (AuthorID,ISBN) VALUES (1,'0132575663') ;
INSERT INTO AuthorISBN (AuthorID,ISBN) VALUES (2,'0132575663') ;
INSERT INTO Titles (ISBN,Title,EditionNumber,Copyright) VALUES ('0132662361','C++ How to Program',8,'2012') ;
INSERT INTO AuthorISBN (AuthorID,ISBN) VALUES (1,'0132662361') ;
INSERT INTO AuthorISBN (AuthorID,ISBN) VALUES (2,'0132662361') ;
INSERT INTO Titles (ISBN,Title,EditionNumber,Copyright) VALUES ('0132404168','C How to Program',6,'2010') ;
INSERT INTO AuthorISBN (AuthorID,ISBN) VALUES (1,'0132404168') ;
INSERT INTO AuthorISBN (AuthorID,ISBN) VALUES (2,'0132404168') ;
INSERT INTO Titles (ISBN,Title,EditionNumber,Copyright) VALUES ('013705842X','iPhone for Programmers: An App-Driven Approach',1,'2010') ;
INSERT INTO AuthorISBN (AuthorID,ISBN) VALUES (1,'013705842X') ;
INSERT INTO AuthorISBN (AuthorID,ISBN) VALUES (2,'013705842X') ;
INSERT INTO AuthorISBN (AuthorID,ISBN) VALUES (3,'013705842X') ;
INSERT INTO AuthorISBN (AuthorID,ISBN) VALUES (4,'013705842X') ;
INSERT INTO AuthorISBN (AuthorID,ISBN) VALUES (5,'013705842X') ;
INSERT INTO Titles (ISBN,Title,EditionNumber,Copyright) VALUES ('0132121360','Android for Programmers: An App-Driven Approach',1,'2012') ;
INSERT INTO AuthorISBN (AuthorID,ISBN) VALUES (1,'0132121360') ;
INSERT INTO AuthorISBN (AuthorID,ISBN) VALUES (2,'0132121360') ;
INSERT INTO AuthorISBN (AuthorID,ISBN) VALUES (3,'0132121360') ;
INSERT INTO AuthorISBN (AuthorID,ISBN) VALUES (4,'0132121360') ;
通过在两个 FOREIGN KEY 语句之后删除NOT NULL
单词并添加ON DELETE CASCADE
和ON UPDATE CASCADE
来解决此问题. /p>
I am not a MySQL professional I am just trying to make simple java application which accesses a MySQL database (e.g. The famous books/authors database) and manipulates it (i.e, add, update, delete).
The problem is that there are two tables that have many to many relationship (e.g. authors table and books table) when I am trying to delete using
DELETE FROM tableName WHERE someColumnName = 'someValue'
it throws MySQLIntegrityConstraintViolationException with the message "Cannot delete or update a parent row: a foreign key constraint fails"
I know it's because of the "many to many" relationship but actually I don't know what to do with this since I am not a MySQL professional. Any help will be appreciated. Thank you.
Edit:here's the database
DROP DATABASE IF EXISTS books;
CREATE DATABASE books;
USE books;
CREATE TABLE Authors
(
AuthorID int NOT NULL AUTO_INCREMENT PRIMARY KEY,
FirstName varchar(30) NOT NULL,
LastName varchar(30) NOT NULL
) ;
CREATE TABLE Titles
(
ISBN varchar(20) NOT NULL PRIMARY KEY,
Title varchar(100) NOT NULL,
EditionNumber int NOT NULL,
Copyright varchar(4) NOT NULL
) ;
CREATE TABLE AuthorISBN
(
AuthorID int NOT NULL,
ISBN varchar(20) NOT NULL,
FOREIGN KEY (AuthorID) REFERENCES Authors(AuthorID) ON DELETE SET NULL,
FOREIGN KEY (ISBN) References Titles(ISBN) ON DELETE SET NULL
) ;
INSERT INTO Authors (FirstName,LastName) VALUES ('Paul','Deitel') ;
INSERT INTO Authors (FirstName,LastName) VALUES ('Harvey','Deitel') ;
INSERT INTO Authors (FirstName,LastName) VALUES ('Abbey','Deitel') ;
INSERT INTO Authors (FirstName,LastName) VALUES ('Michael','Morgano') ;
INSERT INTO Authors (FirstName,LastName) VALUES ('Eric','Kern') ;
INSERT INTO Titles (ISBN,Title,EditionNumber,Copyright) VALUES ('0132152134','Visual Basic 2010 How to Program',5,'2011') ;
INSERT INTO AuthorISBN (AuthorID,ISBN) VALUES (1,'0132152134') ;
INSERT INTO AuthorISBN (AuthorID,ISBN) VALUES (2,'0132152134') ;
INSERT INTO Titles (ISBN,Title,EditionNumber,Copyright) VALUES ('0132151421','Visual C# 2010 How to Program',4,'2011') ;
INSERT INTO AuthorISBN (AuthorID,ISBN) VALUES (1,'0132151421') ;
INSERT INTO AuthorISBN (AuthorID,ISBN) VALUES (2,'0132151421') ;
INSERT INTO Titles (ISBN,Title,EditionNumber,Copyright) VALUES ('0132575663','Java How to Program',9,'2012') ;
INSERT INTO AuthorISBN (AuthorID,ISBN) VALUES (1,'0132575663') ;
INSERT INTO AuthorISBN (AuthorID,ISBN) VALUES (2,'0132575663') ;
INSERT INTO Titles (ISBN,Title,EditionNumber,Copyright) VALUES ('0132662361','C++ How to Program',8,'2012') ;
INSERT INTO AuthorISBN (AuthorID,ISBN) VALUES (1,'0132662361') ;
INSERT INTO AuthorISBN (AuthorID,ISBN) VALUES (2,'0132662361') ;
INSERT INTO Titles (ISBN,Title,EditionNumber,Copyright) VALUES ('0132404168','C How to Program',6,'2010') ;
INSERT INTO AuthorISBN (AuthorID,ISBN) VALUES (1,'0132404168') ;
INSERT INTO AuthorISBN (AuthorID,ISBN) VALUES (2,'0132404168') ;
INSERT INTO Titles (ISBN,Title,EditionNumber,Copyright) VALUES ('013705842X','iPhone for Programmers: An App-Driven Approach',1,'2010') ;
INSERT INTO AuthorISBN (AuthorID,ISBN) VALUES (1,'013705842X') ;
INSERT INTO AuthorISBN (AuthorID,ISBN) VALUES (2,'013705842X') ;
INSERT INTO AuthorISBN (AuthorID,ISBN) VALUES (3,'013705842X') ;
INSERT INTO AuthorISBN (AuthorID,ISBN) VALUES (4,'013705842X') ;
INSERT INTO AuthorISBN (AuthorID,ISBN) VALUES (5,'013705842X') ;
INSERT INTO Titles (ISBN,Title,EditionNumber,Copyright) VALUES ('0132121360','Android for Programmers: An App-Driven Approach',1,'2012') ;
INSERT INTO AuthorISBN (AuthorID,ISBN) VALUES (1,'0132121360') ;
INSERT INTO AuthorISBN (AuthorID,ISBN) VALUES (2,'0132121360') ;
INSERT INTO AuthorISBN (AuthorID,ISBN) VALUES (3,'0132121360') ;
INSERT INTO AuthorISBN (AuthorID,ISBN) VALUES (4,'0132121360') ;
Fixed the problem by removing the NOT NULL
words and adding ON DELETE CASCADE
and ON UPDATE CASCADE
after both FOREIGN KEY statements.
这篇关于在MySQL中从多对多关系中删除的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!