如何在多个表之间强制执行不重复项 [英] How to enforce uniques across multiple tables
问题描述
我在MySQL服务器中有以下表：
公司：pre>
- UID b - NAME
- 其他相关数据
办公室：
- UID（唯一）
- CompanyID
- ExternalID
- 其他数据
员工：
- UID（唯一）
- OfficeID
- ExternalID
- 其他数据
在其中每一个UID都是由数据库创建的唯一标识符。
键以确保在UID上的Employee - > Office - > Company之间的链接。
办公室和员工中的ExternalID字段是公司提供给我的申请我的客户端）。客户端没有（并且不在乎）我自己的ID，我的应用程序从他们收到的所有数据仅根据其ID（即表中的ExternalID）进行标识。
即来自客户端的伪语言请求类似于我是X公司，更新我的员工Y的数据。
我需要强制组合的唯一性of CompanyID和Employees.ExternalID，因此在我的数据库中，同一公司的员工不会有重复的ExternalID。
我在想3种可能的解决方案： p>
- $
将Employees的架构更改为包含CompanyID，并在两个字段上创建唯一约束。
强制执行应用程序级别的检查（即，
我的备用dbadmin-in-me sais（3）是最糟糕的解决方案，因为它在应用程序错误或其他情况下不保护数据库不一致，并且很可能是最慢的数据库。
触发器解决方案可能是我想要的，但它可能变得复杂，特别是如果多个插入/更新需要在单个语句中执行，我不知道性能与（1）。
和（1）看起来最快，最简单的方法，但是反对我对关系模型的理解。
什么SO数据库专家意见是关于利弊特别是如果有可能添加附加级别的间接，即公司 - >办公室 - >部门 - >员工，并且需要保留相同的唯一性（公司/员工）。
解决方案你是对的 - ＃1是最好的选择。
授予，我会问因为快捷方式），但是知道业务规则以确保员工只与一个公司相关 - 这是有道理的。
此外，我有一个外键关联companyid在employee表中的companyid在office表中。否则，您允许员工与没有办公室的公司有关系。除非是可接受的...
如果无法在数据模型中演示关系，触发器是最后的手段，并且从应用程序服务逻辑意味着逻辑是集中式 - 除非有人放弃限制（这意味着你有更大的问题），否则没有发生不良数据的机会。
I have the following tables in MySQL server:
Companies: - UID (unique) - NAME - other relevant data Offices: - UID (unique) - CompanyID - ExternalID - other data Employees: - UID (unique) - OfficeID - ExternalID - other dataIn each one of them the UID is unique identifier, created by the database.
There are foreign keys to ensure the links between Employee -> Office -> Company on the UID.
The ExternalID fields in Offices and Employees is the ID provided to my application by the Company (my client(s) actually). The clients does not have (and do not care) about my own IDs, and all the data my application receives from them is identified solely based on their IDs (i.e. ExternalID in my tables).
I.e. a request from the client in pseudo-language is like "I'm Company X, update the data for my employee Y".
I need to enforce uniqueness on the combination of CompanyID and Employees.ExternalID, so in my database there will be no duplicate ExternalID for the employees of the same company.
I was thinking about 3 possible solutions:
Change the schema for Employees to include CompanyID, and create unique constrain on the two fields.
Enforce a trigger, which upon update/insert in Employees validates the uniqueness.
Enforce the check on application level (i.e. my receiving service).
My alternative-dbadmin-in-me sais that (3) is the worst solution, as it does not protect the database of inconsistency in case of application bug or something else, and most probably will be the slowest one.
The trigger solution may be what I want, but it may become complicated, especially if a multiple inserts/updates need to be performed in a single statement, and I'm not sure about the performance vs. (1).
And (1) looks the fastest and easiest approach, but kind of goes against my understanding of relational model.
What SO DB experts opinion is about pros and cons of each of the approaches, especially if there is a possibility for adding an additional level of indirection - i.e. Company -> Office -> Department -> Employee, and the same uniqueness needs to be preserved (Company/Employee).
解决方案You're right - #1 is the best option.
Granted, I would question it at first glance (because of shortcutting) but knowing the business rule to ensure an employee is only related to one company - it makes sense.Additionally, I'd have a foreign key relating the companyid in the employee table to the companyid in the office table. Otherwise, you allow an employee to be related to a company without an office. Unless that is acceptable...
Triggers are a last resort if the relationship can not be demonstrated in the data model, and servicing the logic from the application means the logic is centralized - there's no opportunity for bad data to occur, unless someone drops constraints (which means you have bigger problems).
这篇关于如何在多个表之间强制执行不重复项的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
