java jdbc中的外键问题
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4120482/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
foreign key problem in jdbc
提问by Andrei Zhaleznichenka
I have two functions:
我有两个功能：
public void Populate_flights()
public void Populate_reservations()
Flight and reservations are two tables.One of the entry i.e flight no. is in the reservation table. So it is a foreign key.
航班和预订是两个表。其中一个条目即航班号。在预订表中。所以它是一个外键。
Now, I need to populate the database via jbdc. So I am using: In public void Populate_reservations() function:
现在，我需要通过 jbdc 填充数据库。所以我使用：在 public void Populate_reservations() 函数中：
Statement s = conn.createStatement();
s.executeUpdate("DELETE FROM reservations");
public void Populate_flights() -:
public void Populate_flights() -：
Statement s = conn.createStatement();
s.executeUpdate("DELETE FROM flights");
So in this way, before populating the database, all my previous entries are removed and no redundant data is there.Since, there is a foreign key in reservation table, I can't delete entries from flight first. I have to remove entries from reservation first. But reservation function is called after flight function.SO how would I make it so that it will delete all the entries.
所以这样，在填充数据库之前，我之前的所有条目都被删除了，并且没有多余的数据。由于预订表中有一个外键，我不能先从航班中删除条目。我必须先从预订中删除条目。但是预订功能是在航班功能之后调用的。所以我将如何使它删除所有条目。
So it should be like this:
所以它应该是这样的：
Statement s = conn.createStatement();
s.execute("SET FOREIGN_KEY_CHECKS=0");
s.executeUpdate("DELETE FROM flights");
s.execute("SET FOREIGN_KEY_CHECKS=1");
回答by Alex Jasmin
You can temporary disable foreign key checks in MySQL to perform operations that would fail if these checks were enabled:
您可以在 MySQL 中临时禁用外键检查以执行如果启用这些检查将会失败的操作：
// Disable foreign keys check
Statement stmt = conn.createStatement();
stmt.execute("SET FOREIGN_KEY_CHECKS=0");
stmt.close();
// Do your stuff
// Enable foreign keys check
Statement stmt = conn.createStatement();
stmt.execute("SET FOREIGN_KEY_CHECKS=1");
stmt.close();
Note that this is a per connection setting so you have to do all your stuff using the same connobject.
请注意，这是每个连接的设置，因此您必须使用相同的conn对象完成所有工作。
回答by BalusC
So you want to cascadethe foreign key references on delete. You have to set it on the foreign key constraint. First drop the old constraint and then recreate it with the cascade instruction. Assuming that the FK name is fk_flight, here's an example:
所以你想在删除时级联外键引用。您必须在外键约束上设置它。首先删除旧约束，然后使用级联指令重新创建它。假设 FK 名称为fk_flight，这是一个示例：
ALTER TABLE reservations
DROP CONSTRAINT fk_flight;
ALTER TABLE reservations
ADD CONSTRAINT fk_flight
FOREIGN KEY (flight_id)
REFERENCES flight(id)
ON DELETE CASCADE;
This way all referenced reservations will be deleted if you delete alone the flight.
这样，如果您单独删除航班，所有引用的预订都将被删除。
回答by Andrei Zhaleznichenka
Statement s = conn.createStatement();;
s.addBatch("SET FOREIGN_KEY_CHECKS = 0");
s.addBatch("DELETE FROM reservations");
s.addBatch("DELETE FROM flights");
s.addBatch("SET FOREIGN_KEY_CHECKS = 1");
s.executeBatch();
