无法删除或更新父行:在Java JDBC中存在外键约束失败。

huangapple go评论75阅读模式
英文:

Cannot delete or update a parent row: a foreign key constraint fails in java jdbc

问题

我是SQL的完全新手,我将一个Java应用程序连接到一个MySQL的XAMPP服务器。现在我有2个表,一个是顾客表,另一个是电子邮件表。

这是我表格的可视化表示:

无法删除或更新父行:在Java JDBC中存在外键约束失败。

在我的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

无法删除或更新父行:在Java JDBC中存在外键约束失败。

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&lt;String&gt; deleteCustomers = new ArrayList&lt;&gt;();
 //... add values to deleteCustomers and then proceed to delete 
try {
    				for(String id : deleteCustomers) {
    					String delCustomers = &quot;DELETE FROM customer WHERE customer_id = ?&quot;;
    					state=connection.prepareStatement(delCustomers);
    					state.setString(1, id);
    					state.executeUpdate();
    				}
    			 }catch(SQLException e ) {
    				 System.out.println(&quot;Could not delete customer data &quot; +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

huangapple
  • 本文由 发表于 2020年9月11日 16:42:05
  • 转载请务必保留本文链接:https://go.coder-hub.com/63843656.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定