SQL数据库同步时如何保留外键关系 [英] how to retain foreign key relationship while SQL Database sync

问题描述

我有以下情况:
我的客户在不同位置有2个办公室，它们在数据连接方面没有专门地相互连接.让我们把这些办公室称为HO和Branch
HO将通常负责输入与应用程序配置有关的主数据.而且两个办事处都将输入各自地理区域的客户的主数据和交易数据.
在分支机构的交易表中输入的数据需要经过HO的人员批准.
同样，分支机构至少需要访问有关应用程序配置的应用程序中输入的主数据.
现在的问题是如何同步两个办公室中的两个数据库，以便在表中维护外键关系. 例如可以说，数据库中两个位置都有两个表，每个表将从两个办公室获取数据.
表1Client_master
表2Client_transaction_Header
表3Client_transaction_Details

可以说以下各列:
Client_master
Client_ID int(自动递增主键)
Client_Name Varchar(50)

Client_Transaction_Header
CL_TR_HD_ID int(自动递增主键)
CL_TR_Client_ID int(表1的外键)
CL_TR__Hd_Date日期时间
CL_TR__HD_ship_Addr Varchar(50)

Client_transaction_Details
CL_TR_Det_ID int(自动递增主键)
CL_TR_HD_ID int(表2的外键)
CL_TR_Det_item Varchar(40)
CL_TR_DET_Price小数

如果两个办公室都将在上述所有表中输入数据，我该如何同步数据?

我正在将Windows Forms应用程序与.net Framework 3.5代码语言C#和SQL Edition 2005 Std
一起使用
限制条件:
无法使用Web应用程序
无法使用单个SQL数据库服务器.
如果可能的话，将要使用SQL Express Edition.
准备从2005 SQL Server迁移到2008 SQL Server.

I have following scenario:
My client has 2 offices at different locations not dedicatedly connected to each other in terms of data connectivity. Lets call these offices as HO and Branch
The HO will be generally responsible in entering the master data related to the application configuration. And both the offices will be entering, both master and transaction data for their respective clients of their geography.
The data entered in the transaction table at the Branch office needs to be approved by a person sitting at the HO.
Also the Branch needs to access at least the Master data entered in the application with regards to the application configuration.
Now the problem is how do I sync both the databases in both the offices such that the foreign key relation ship is maintained in the tables.
For e.g. lets say that there are 2 tables in the database at both the location each of which will be getting data from both the offices.
Table 1  Client_master
Table 2  Client_transaction_Header
Table 3 Client_transaction_Details

Lets say the following are the columns:
Client_master
Client_ID int (Auto increment Primary key)
Client_Name Varchar(50)

Client_Transaction_Header
CL_TR_HD_ID int (auto increment Primary Key)
CL_TR_Client_ID int (Foreign key of table 1)
CL_TR__Hd_Date Datetime
CL_TR__HD_ship_Addr Varchar(50)

Client_transaction_Details
CL_TR_Det_ID int (Auto increment Primary Key)
CL_TR_HD_ID int (foreign key of Table 2)
CL_TR_Det_item Varchar(40)
CL_TR_DET_Price decimal

How do I sync data if both the offices will enter data in all the above tables?

I am using Windows Forms application with .net framework 3.5 code language c# and SQL Edition 2005 Std

Constraints:
Cannot use Web application
Cannot use single SQL Database server.
If possible would want to use SQL Express Editions.
Ready to migrate from 2005 to 2008 SQL Server.

推荐答案

您不能仅将自动增量字段用作主键-合并数据时，您需要采取其他措施来避免冲突.

按照我的个人喜好，有两种相对简单的方法来处理此问题:
-切换为使用uniqueidentifier字段，然后在您的C#代码中将它们设置为新的GUID.这种方法具有避免意外FK匹配的额外安全性，这种情况在使用正整数时可能会发生.
-如果必须使用自动递增字段，请添加第二列(称为OFFICE_ID)，并将其作为两列键的一部分.在HO中将OFFICE_ID设置为1，在Branch中将OFFICE_ID设置为2.这不太方便，因为您的所有外键也都变成了复合键.

我在遥远的过去(超过10年前)使用了第三种方法，这是最不可靠的方法:我们为多个安装中的每一个分配了唯一的编号N，并为N * 10 ^ 9中的所有表启动了自动递增序列.每个站点限制为10 ^ 9条记录，并且在生产和开发站点之间复制数据库进行测试时，我们经常遇到愚蠢的错误.

You cannot use autoincrement fields alone for your primary keys - you need something else to avoid collisions when merging the data.

There are two relatively simple ways of dealing with this issue, in the order of my personal preference:
- Switch to using uniqueidentifier fields, and set them to a new GUID in your C# code. This approach has an added security of avoiding unintended FK matches, which might happen when you use straight integers.
- If you must use an autoincrement field, add a second column (call it OFFICE_ID), and make it part of a two-column key. Set OFFICE_ID to 1 in HO, and to 2 in the Branch. This is a lot less convenient, because all your foreign keys become composite as well.

I used a third approach in the remote past (more than 10 years ago), and it was the least reliable: we assigned each of our several installations a unique number N, and started our autoincrement sequences for all tables in N*10^9. There was a limit of 10^9 records per site, and we constantly ran into silly errors when copying DBs for testing between production and development sites.

这篇关于SQL数据库同步时如何保留外键关系的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！