填充数据库中的外键问题 [英] problem with foreign key in populating database
问题描述
Deleting previous tuples from flights
Error: com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationExcepti
on: Duplicate entry 'AAH196' for key 'PRIMARY'
com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Dupl
icate entry 'AAH196' for key 'PRIMARY'
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(Unknown Source)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Sou
rce)
at java.lang.reflect.Constructor.newInstance(Unknown Source)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:409)
at com.mysql.jdbc.Util.getInstance(Util.java:384)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1041)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3566)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3498)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1959)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2113)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2562)
at com.mysql.jdbc.StatementImpl.executeUpdate(StatementImpl.java:1664)
at com.mysql.jdbc.StatementImpl.executeUpdate(StatementImpl.java:1583)
at Populate.Populate_flights(Populate.java:94)
at Populate.<init>(Populate.java:36)
at Populate.main(Populate.java:28)
Deleting previous tuples from reservationsInserting Data into table building Err
or 2: com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException
: Cannot add or update a child row: a foreign key constraint fails (`cs585hw3`.`
reservations`, CONSTRAINT `reservations_ibfk_1` FOREIGN KEY (`flight_no`) REFERE
NCES `flights` (`flight_no`))
com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Cann
ot add or update a child row: a foreign key constraint fails (`cs585hw3`.`reserv
ations`, CONSTRAINT `reservations_ibfk_1` FOREIGN KEY (`flight_no`) REFERENCES `
flights` (`flight_no`))
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(Unknown Source)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Sou
rce)
at java.lang.reflect.Constructor.newInstance(Unknown Source)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:409)
at com.mysql.jdbc.Util.getInstance(Util.java:384)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1041)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3566)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3498)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1959)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2113)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2562)
at com.mysql.jdbc.StatementImpl.executeUpdate(StatementImpl.java:1664)
at com.mysql.jdbc.StatementImpl.executeUpdate(StatementImpl.java:1583)
at Populate.Populate_reservations(Populate.java:126)
at Populate.<init>(Populate.java:37)
at Populate.main(Populate.java:28)
我有两个桌子. flight.data和Reservations.data.我正在尝试通过jdbc填充表.填充它时,出现上述错误.
I have two tables. flights.data and reservations.data. I am trying to populate the tables via jdbc. While populating it, I am getting the above error.
从预订到航班都有外键引用.我必须先从辅助表中删除记录.这意味着,我应该先从预订中删除记录,然后再从航班中删除.那是对的吗?我检查了我的表,预订表有一个外键.所以我必须先从该表中删除数据.
There is a foreign key reference from reservations to flights.I have to delete records from the secondary table first. This would mean, I should delete records from reservations before I delete from flights. Is that Correct? I checked my table, reservations table has a foreign key. SO I have to delete the data first from this table.
但是我该如何执行此任务?
But How do I perform this task?
在预订功能中,我是这样的
In the reservation function, I doing like this,
Statement s = conn.createStatement();
s.executeUpdate("DELETE FROM reservations");
在table函数中,就像这样,相反,我使用了从表中删除.请告诉我如何解决.
In the table function, it is same like this , instead , I used delete from table.Please tell how would I fix it.
推荐答案
您知道要在排期中尝试删除的行的主键吗?假设是5.
Do you know the primary key of the row you're trying to delete in flights? Let's say it is 5.
long flightId = 5;
Statement s = c.prepareStatement("DELETE FROM reservations WHERE flight_id = ?");
s.setLong(1,flightId);
s.executeUpdate();
此时,您可以删除航班,因为没有保留指向该航班.
At that point, you can delete your flight because there will be no reservations pointing back to it.
s.prepareStatement("DELETE FROM flights WHERE id = ?");
s.setLong(1, flightId);
s.executeUpdate();
如果要从两个表中删除所有日期,则:
if you want to remove all date from both tables:
Statement s = c.prepareStatement("DELETE FROM reservations");
s.executeUpdate();
s = c.prepareStatement("DELETE FROM flights");
s.executeUpdate();
这篇关于填充数据库中的外键问题的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!