SQL 表设计 - 标识列
SQL Server 2008 数据库问题。
我有 2 个表，出于参数考虑，称为“客户”和“用户”，其中单个客户可以拥有 1 到 n 个用户。 Customers 表生成一个 CustomerId，它是一个带有 +1 增量的种子身份。我在 Users 表中找到的是一个由 CustomerId 和序列号组成的复合键，这样在所有情况下，第一个用户的序列都是 1，后续用户将在 x+1 处添加。
所以表格看起来像这样...
- CustomerId (PK, FK)
- UserId (PK)
- Name
...例如，如果 Customer 485 有三个客户，数据将如下所示...
CustomerId | UserId | Name
----------
485 | 1 | John
485 | 2 | Mark
485 | 3 | Luke
我很感激我可以手动添加 1 ,2,3,...,n UserId 条目，但是我希望在 SQL 中插入行时自动发生这种情况，以便在显示的示例中我可以有效地插入带有 CustomerId 和 Name 的行，并受 SQL Server 保护有没有办法通过数据库设计本身来做到这一点 - 当我将 UserId 设置为身份时，它在所有客户中运行 1 到无穷大，这不是我想要的 - 我是否在某个地方设置错误，或者这不是一个选择？
希望这是有道理的 - 感谢您的帮助
SQL Server 2008 Database Question.
I have 2 tables, for arguments sake called Customers and Users where a single Customer can have 1 to n Users. The Customers table generates a CustomerId which is a seeded identity with a +1 increment on it. What I'm after in the Users table is a compound key comprising the CustomerId and a sequence number such that in all cases, the first user has a sequence of 1 and subsequent users are added at x+1.
So the table looks like this...
- CustomerId (PK, FK)
- UserId (PK)
- Name
...and if for example, Customer 485 had three customers the data would look like...
CustomerId | UserId | Name
----------
485 | 1 | John
485 | 2 | Mark
485 | 3 | Luke
I appreciate that I can manually add the 1,2,3,...,n entry for UserId however I would like to get this to happen automatically on row insert in SQL, so that in the example shown I could effectively insert rows with the CustomerId and the Name with SQL Server protecting the Identity etc. Is there a way to do this through the database design itself - when I set UserId as an identity it runs 1 to infinity across all customers which isn't what I am looking for - have I got a setting wrong somewhere, or is this not an option?
Hope that makes sense - thanks for your help
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
如果不实现插入行并检查以适当增加 Id 的自定义存储过程，我想不出自动的方法来做到这一点，尽管其他有更多知识的人可能有更好的想法。
然而，这对我来说有点自然化代理键的味道 - 这并不总是一个好主意。
更多信息请参见：
http://www.agiledata.org/essays/keys.html
I can think of no automatic way to do this without implementing a custom Stored Procedure that inserted the rows and checked to increment the Id appropriately, althouh others with more knowledge may have a better idea.
However, this smells to me of naturalising a surrogate key - which is not always a good idea.
More info here:
http://www.agiledata.org/essays/keys.html
这实际上并不是一个带有常规身份列的选项，但您可以设置一个插入触发器来自动填充用户 ID。
执行此操作的简单方法是让触发器从用户表中为插入的记录上的客户 ID 选择最大用户 ID，然后为其添加 1。但是，如果多个人同时创建用户记录，您将遇到并发问题。
更好的解决方案是在客户表上有一个 NextUserID 列。在您的触发器中，您将：
这应该确保同时添加用户不会导致同一用户 ID 被多次使用。
综上所述，我建议您不要这样做。这带来的麻烦大于其价值，而且一开始就听起来像是个坏主意。
That's not really an option with a regular identity column, but you could set up an insert trigger to auto populate the user id though.
The naive way to do this would be to have the trigger select the max user id from the users table for the customer id on the inserted record, then add one to that. However, you'll run into concurrency problems there if more than one person is creating a user record at the same time.
A better solution would be to have a NextUserID column on the customers table. In your trigger you would:
This should ensure that simultaneous additions of users don't result in the same user id being used more than once.
All that said, I would recommend that you just don't do it. It's more trouble than it's worth and just smells like a bad idea to begin with.
因此，您需要生成一个在 customer_id 范围内递增的 user_id 字段。
我想不出有哪个数据库存在这个概念。
您可以使用触发器来实现它。但我的问题是：为什么？
代理键不应该具有任何意义。你为什么要尝试制作一个既是代理又意味着顺序的键？
我的建议：
创建一个 date_created 字段，默认为 getDate()。这将使您了解每个 user_id 的创建顺序（基于时间）。
创建一个序数字段 - 可以由触发器更新，以支持该顺序。
希望有帮助。
So you want a generated user_id field that increments within the confines of a customer_id.
I can't think of one database where that concept exists.
You could implement it with a trigger. But my question is: WHY?
Surrogate keys are supposed to not have any kind of meaning. Why would you try to make a key that, simultaneously, is the surrogate and implies order?
My suggestions:
Create a date_created field, defaulting to getDate(). That will allow you to know the order (time based) in which each user_id was created.
Create an ordinal field - which can be updated by a trigger, to support that order.
Hope that helps.