外键参考目标不存在 [英] Foreign key reference target does not exist

问题描述

我需要同步来自不同Firebird数据库的两个表中的数据.准确地说，我需要使用表Users(第二数据库)中的记录来更新表Person(第一数据库)中的记录.不仅是名称"，电子邮件"，生日"，而且ID也是(！).问题是-有一些表，这些表依赖于通过FOREIGN KEY Constraint的Person的ID.

I need to synchronize data in two tables from different Firebird databases. Precisely, I need to update records in table Person (1st DB), using records from table Users (2nd DB). Not only "name", "email", "birthday", but ID also (!). The problem is - there are tables, that depend on Person's IDs through FOREIGN KEY Constraint.

我正在尝试这样做:

1. 将外键约束放到相关表中.
2. 同步两个表(这意味着更新/更改表Person中的ID)
3. 更改相关表中的适当ID.
4. 在相关表中添加外键约束.

最后一步会导致错误(来自Java应用程序的日志，但是如果我直接在IBExpert中直接执行这些步骤，则是相同的):

Last step causes a error (log from java application, but it's the same if I perform these steps directly in IBExpert):

com.bssys.db.jdbc.DBSQLException: GDS Exception. 335544466. violation of FOREIGN KEY constraint "FK_EMPLOYEE_PERSON" on table "EMPLOYEE"
Foreign key reference target does not exist, error code: HY000
Reason: violation of FOREIGN KEY constraint "FK_EMPLOYEE_PERSON" on table "EMPLOYEE"

(雇员-是相关表之一)

(Employee - is one of the dependent tables)

我的问题是，是否可以避免此错误.或者，也许有人对如何更改相关表中的ID有一些想法.可能有特殊的RDBMS工具来同步数据库，但是我需要通过Java应用程序来同步它们，因此仅使用sql和java.我使用Firebird 2.5.1.

My question is, whether I can avoid this error. Or maybe there are some ideas on how to change IDs in related tables. There are probably special RDBMS tools to sync databases, but I need to sync them through Java application, therefore using only sql and java. I use Firebird 2.5.1.

完整的SQL语句(示例):

Complete SQL-statements (example):

ALTER TABLE employee DROP CONSTRAINT fk_employee_person

UPDATE person SET id = 555555 WHERE id = 3000005

UPDATE employee SET person_id = 555555 WHERE person_id = 3000005

ALTER TABLE employee ADD CONSTRAINT fk_employee_person FOREIGN KEY (person_id) REFERENCES person(id)

一些新信息:有时IBExpert似乎允许我完成这些步骤.实际上，如果在其中一个表处于"DATA"模式下更改了所有ID的情况下，这会产生错误(我想，这也是某种事务，例如 CREATE VIEW ).

Some new information: it seems like sometimes IBExpert allows me to go through these steps. Actually, it gives an error in case I changed all IDs while one of the tables were in the "DATA" mode (I suppose, it's also some kind of transaction, like CREATE VIEW).

我还发现，删除/添加外键至少在Firebird 2.1(甚至2.5)之前需要对整个数据库进行独占锁定

I also found out that removing/adding Foreign Key requires exclusive lock on the whole database at least till Firebird 2.1 (or even 2.5)

推荐答案

您应该创建表，以便外键确实具有 ON UPDATE CASCADE 子句-然后在更新 ID ，它也将在从属表中进行更新，而无需您进行任何额外的工作.因此，对于引用 Person 表的每个表，您需要执行以下操作:

You should create your tables so that foreign keys do have ON UPDATE CASCADE clause - then when you update the ID it is updated in the dependent tables as well, without any additional effort on your part. So for each table which refer to the Person table you need to do following:

-- delete the original FK constraint
ALTER TABLE _table_ DROP CONSTRAINT _fk_constraint_name_;
-- (re)add the FK constraint with ON UPDATE CASCADE
ALTER TABLE _table_ ADD CONSTRAINT _fk_constraint_name_ FOREIGN KEY (person_id) REFERENCES person(id) ON UPDATE CASCADE;

这篇关于外键参考目标不存在的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！