英文:
Cannot delete or update a parent row: a foreign key constraint fails in java jdbc
问题
我是SQL的完全新手,我将一个Java应用程序连接到一个MySQL的XAMPP服务器。现在我有2个表,一个是顾客表,另一个是电子邮件表。
这是我表格的可视化表示:
在我的Java应用程序中,我有一个顾客ID列表,我想要删除这些顾客,并且当我运行查询时,我得到以下错误:
Could not delete customer data java.sql.SQLIntegrityConstraintViolationException: Cannot delete or update a parent row: a foreign key constraint fails (`airline_db`.`email_address`, CONSTRAINT `email_address_customer_fk` FOREIGN KEY (`customer_customer_id`) REFERENCES `customer` (`customer_id`))
我的代码:
ArrayList<String> deleteCustomers = new ArrayList<>();
//... 向deleteCustomers添加要删除的值,然后继续删除
try {
for(String id : deleteCustomers) {
String delCustomers = "DELETE FROM customer WHERE customer_id = ?";
state=connection.prepareStatement(delCustomers);
state.setString(1, id);
state.executeUpdate();
}
} catch(SQLException e ) {
System.out.println("Could not delete customer data " + e);
}
看起来,由于电子邮件表以customer_id作为外键从customer表中引用,我无法删除顾客条目。我不知道该如何处理这个问题,由于我是个新手,希望能得到你的帮助。
英文:
I'm a complete beginner in sql and I connect a java app to a mysql xampp server . Now I have 2 tables a table of customers and a table of emails .
This is a visual representation of my tables
In my java app I have a list of customer ids I want to delete and when I run my query I get
Could not delete customer data java.sql.SQLIntegrityConstraintViolationException: Cannot delete or update a parent row: a foreign key constraint fails (`airline_db`.`email_address`, CONSTRAINT `email_address_customer_fk` FOREIGN KEY (`customer_customer_id`) REFERENCES `customer` (`customer_id`))
My code :
ArrayList<String> deleteCustomers = new ArrayList<>();
//... add values to deleteCustomers and then proceed to delete
try {
for(String id : deleteCustomers) {
String delCustomers = "DELETE FROM customer WHERE customer_id = ?";
state=connection.prepareStatement(delCustomers);
state.setString(1, id);
state.executeUpdate();
}
}catch(SQLException e ) {
System.out.println("Could not delete customer data " +e);
}
It seems that since the email table takes the customer_id from customer as a foreign key I cannot delete a customer entry . I do not know how to deal with this and I would appreciate your help since I am a beginner .
答案1
得分: 0
因为customer_id在您的EMail-Table中被用作外键,所以您不能删除它。因此,您首先需要删除所有使用要删除的customer_id的电子邮件,然后再删除Customer-Table中的customer_id。
英文:
Because the customer_id is used as a foreign key in your EMail-Table, you cannot delete it. Therefore you first need to delete all emails that use the customer_id you want to delete, and then delete the customer_id in the Customer-Table
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论