将AutoIncrement限制在特定范围内 [英] Limiting AutoIncrement to a specific range
问题描述
我正在尝试创建一个工作应用程序。该应用程序将在内部使用，并应允许我们为我们的产品SKU分配一些条形码编号。我正在使用Visual Studio / Basic 2010 Express来构建它，因为我非常有限，初学者的经验是与VS 2010 Express。
I am trying to create an application for work. The app will be used internally and should allow us to assign some barcode numbers to our product SKUs. I am using Visual Studio / Basic 2010 Express to build this as my very limited and beginners experience is with VS 2010 Express.
我将给出一些有关如何我看到这个应用程序正在工作，然后我会得到我的实际问题：
I'll give a bit of information about how I see this application working and then I'll get on with my actual question:
我看到应用程序允许我们在用户输入的数据库中创建一个新的产品SKU和产品描述，然后应用程序将为该条形码分配下一个可用的基数，并从应用程序将（如果需要）生成正确的EAN13和GTIN14条形码并将其存储在该SKU上。
I see the app allowing us to create a new Product in the database by a user entering the SKU and description of the product and then the app will assign this product the next available base number for the barcode and from there the app will (if required) generate the correct EAN13 and GTIN14 barcodes and store them against that SKU.
作为一家公司，我们可以使用大范围的条形码数据，我们已经将这个大范围的分割，以便第一个50,000（例如）是我们的EAN13代码，接下来的50K是用于内箱的GTIN14代码，其余的50K用于主纸箱。
As a company we have a large range of barcode numbers we can use and we have split this large range up so that the first 50,000 (for example) are for our EAN13 codes, the next 50K are for our GTIN14 codes for Inner Cartons and the remaining 50K are for Master Cartons.
所以为了实现这个，我有我的产品表，其中包含字段' SKU'，'Description'和'BarcodeBase'。我设法将BarcodeBase字段设置为唯一，并且我正在尝试使用AutoIncrement（种子和步骤）来确定产品是否符合EAN13范围内的基本条形码（在计算校验位之前）以上...
So in order to achieve this I have my Product table which contains the fields 'SKU', 'Description' and 'BarcodeBase'. I have managed to set the BarcodeBase field as unique and I am attempting to use AutoIncrement(Seed & Step) to make sure that this assigns the product a base barcode (before I calculate the check digit) that falls within the EAN13 range as described above...
所以最后我的问题是：有没有办法可以把自动增值的上限放在这个机会上在未来的方式方式，基本的条形码数字不会溢出到下一个范围？
So finally my question is: Is there a way I can put an upper limit on AutoIncrement so that on the off chance, way way in the future, the base barcode number will not overflow into the next range?
我一直在谷歌搜索不成功的答案，我只是遇到了事情其涉及具有限制的字段的数据类型。例如Int32类型的上限。通过我的搜索，我已经变得模糊地意识到该领域的表达属性，也是编码部分类的可能性 - 但是我不知道这是否是正确的方向，或者如果有更简单的方法我看不到/没有找到。
I've been googling unsuccessfully for an answer and I am only coming across things which talk about the data type of the field having a limit. For example the upper limit of an Int32 type. Through my searches I have become vaguely aware of the 'Expression' property of the field and also the possibility of coding a partial class - but I don't know if that is the right direction to go in or if there is something much simpler that I am overlooking / have not found.
我真的很感激任何帮助！
I would really appreciate any help!
编辑：根据GrandMasterFlush的评论 - 我已将本地数据库添加到我的VS项目。所以我想我正在使用SQL Server Compact 3.5分贝。
As per GrandMasterFlush's comment - I have added a local database to my VS project. So I think I am using a SQL Server Compact 3.5 db.
推荐答案
使用CHECK约束，例如：
Use a CHECK constraint, e.g.:
ALTER TABLE dbo.Product ADD CONSTRAINT ...
CHECK (BarcodeBase BETWEEN 1 AND 50000);
我建议您不要在产品表中创建条形码库IDENTITY列（IDENTITY是您的功能指的是自动增量）。 IDENTITY真正专为替代密钥使用，并不适用于有意义的业务数据。您不能更新IDENTITY列，它不一定是顺序的，可能在数字序列中有空白，您也只能为每个表使用一个IDENTITY列。而不是在Product表中使用IDENTITY，您可以在其他地方生成序列，例如通过递增存储在单个行表中的单个值。
I suggest you do not make BarcodeBase an IDENTITY column in the Product table (IDENTITY is the feature that you are referring to as "autoincrement"). IDENTITY is really designed for surrogate key use only and isn't ideal for meaningful business data. You can't update an IDENTITY column, it isn't necessarily sequential, may have gaps in the number sequence and you also only get to use one IDENTITY column per table. Instead of using IDENTITY in the Product table you can generate the sequence elsewhere, for example by incrementing a single value stored in a single row table.
这篇关于将AutoIncrement限制在特定范围内的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
