我怎样才能在许多表中拥有唯一的列
我有十个或更多（我不知道）表，其中有一个名为 foo 的列，具有相同的数据类型。
我如何告诉 sql 所有表中的值应该是唯一的。
我的意思是如果（我在表1中具有值“1”）我不应该能够在表2中具有值“1”
I have ten or more(i don't know) tables that have a column named foo with same datatype.
how can i tell sql that values in all the tables should be unique.
I mean If(i have value "1" in table1) I should NOT be able to have value "1" in table2
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（5）
有一个共同的ID表，这十个表都引用它。这会很好地工作，因为它将确保唯一的 ID，但并不意味着如果有人真的想要的话，您不能复制表中的 ID。
我的意思是，公共 ID 的表可确保插入时不会出现重复项（通过将 ID 插入到此公共表中），但问题是保证这种情况永远不会发生的方法是将业务规则构建到系统或放置检查约束来交叉引用其他表（这将确保唯一性，但会降低性能）。
Have a common ID's table, which these ten tables reference. That will work well in that it will ensure unique ID's, but doesn't mean you couldn't duplicate the ID's in the table if someone really wants to.
What I mean is a common ID's table ensures that you don't have duplicates for insert (by also inserting an ID into this common table), but the thing is the way to guarantee that it never happens is by building the business rules into the system or placing check constraints to cross reference the other tables (which would ensure uniqueness, but degrade performance).
这个问题的措辞含糊；如果需要生成在多个表中唯一的列，请使用行 GUID 或通用 ID 生成器表；如果您需要强制唯一性（并且字段值已经存在），请使用触发器。
一般来说，如果您生成这些值，则无需强制执行任何操作。如果正确的话，生成逻辑将会解决这个问题。例如，如果您要插入用户输入，那么您可以而且应该在插入过程中强制执行唯一性。作为验证规则或其他东西。
The question is phrased vaguely; if you need to generate a column that's unique among several tables, use row GUIDs or a common ID generator table; if you need to enforce uniqueness (and the field values are already there), use triggers.
Generally, if you generate the values, you don't need to enforce anything. The generation logic, if done right, will take care of that. If you are inserting, say, user input, then you can and should enforce uniqueness during insertion. As a validation rule or something.
您可以将该字段定义为 GUID（或 SQL Server 中的 UNIQUEIDENTIFIER）。那么无论如何，它都将永远是独一无二的。
You can define the field as a GUID (or a UNIQUEIDENTIFIER in SQL server). Then it will always be unique no matter what.
如何在每个表上设置一个检查约束，使得 ID % 10 = N（其中 N 是表编号，从 0 到 9）。每次都使用 IDENTITY(N,10)。
How about setting a check constraint on each table, such that ID % 10 = N (where N is the table number, from 0-9). And use IDENTITY(N,10) each time.
我建议你的设计可能有缺陷。为什么这些表是单独的？最好将它们放在一个带有一个 id 字段和另一个字段的表中，以识别构成这些单独表的内容（例如客户 ID）。然后，如果您出于性能原因希望客户对分区表进行拆分，您可以阅读有关分区表的信息。
I would suggest that possibly your design is flawed. Why are these separate tables? It ouwld be better to put them in one table with one id field and another filed to identify whatever is making these spearate tables (cusotmer id for instance). Then you can read about partioning tables if you want them to be split by customer for performance reasons.