如何在两个表中具有唯一的主键? [英] How to have unique primary key in two tables?

问题描述

我的系统EMPLOYEE和EMPLOYEE_FORECAST中有两个表.两者具有相同的列，整个结构相同.我还有另一个具有相同结构的存档表，称为EMPLOYEE_ARCHIVE表.

I have two tables in my system EMPLOYEE and EMPLOYEE_FORECAST. Both have the same columns, entire structure is same. I have another archive table with same structure called EMPLOYEE_ARCHIVE table.

我需要将两个表中的数据放入此存档表中.由于EMPLOYEE和EMPLOYEE_FORECAST中的记录可能具有相同的主键，例如EMPLOYEE中的一条记录的pk表示为100，而EMPLOYEE_FORECAST中的另一条记录的pk也可能为100，这肯定会发生，因此当将它们插入存档表时，我将有一个重复的主键.

I need to put data from both tables to this archive table. Since records in EMPLOYEE and EMPLOYEE_FORECAST may have same primary key e.g. a record in EMPLOYEE will have a pk of say 100 and another record in EMPLOYEE_FORECAST may also have pk of 100 and this will definitely happen so when they are inserted into archive table I will have a duplicate primary key.

问题是我还会有一些关系表，例如employee_products，employee_forecast_products和employee_archive_products.这些表将具有emp_id和product_id.因此，使用相同的emp_id，我将无法找出确切的员工.

The problem is I will also have some relation table like employee_products, employee_forecast_products and also employee_archive_products. These tables will have emp_id and product_id. So with same emp_id I wont be able to figure out the exact employee.

因此，有什么方法可以同时具有EMPLOYEE和EMPLOYEE_FORECAST表的唯一主键.

So, is there any way to have a unique primary key both the EMPLOYEE and EMPLOYEE_FORECAST tables.

推荐答案

您的数据模型似乎有点困惑.如果EMPLOYEE和EMPLOYEE_FORECAST具有相同的结构，为什么要有两个表?这里的业务规则是什么?

Your data model seems a tad confused. If EMPLOYEE and EMPLOYEE_FORECAST have identical structures why have two tables? What is the business rule here?

如果应该将它们作为两个单独的表，为什么要将它们存储在公共归档表中?为什么不为每个表单独归档?

And if they are supposed to be two separate tables why store them in a common archive table? Why not have separate archives for each table?

我同意@Ollie.您需要重新考虑数据模型，以便清楚地表达您的业务运作方式.如果您在此处发布业务规则，我相信我们可以帮助您解决问题.但这可能是一个关键的问题:以下密钥可以识别一个雇员(即现实世界中的一个人)还是两个雇员?

I agree with @Ollie. You need to rethink your data model so it clearly expresses how your business operates. If you post your business rules here I'm sure we can help you untangle things. But here is probably the crucial question: do the following keys identify one employee (i.e one person in the real world) or two?

employee.emp_id = 100
employee_forecast.emp_id = 100

这篇关于如何在两个表中具有唯一的主键?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！