SQL Server 2005自动更改入门密钥 [英] Sql Server 2005 Auto changing primery key

问题描述

hi
我正在使用sql server 2005和C#.net，我有一个表名Patient，该表的主键是(LAB_Id int)，它的自动增量为true，并且将一列的值种子为11000001,11000002,11000003. ........依此类推，现在我希望当年份从2011年更改为2012年时，我的LAB_Id自动从0开始，但是像这样12000001、12000002、12000003 ............ .so并且在2013年，它应该以13000001、13000002、13000003 .......开头.这意味着前两位代表年份，并且随着年份的变化，它应该自动更改. br/> 是否可以通过代码

hi
i am using sql server 2005 and C#.net, i have a table name patient the primary key of this table is (LAB_Id int) which auto increment is true and seed in one the value of this column is like 11000001,11000002,11000003.........and so on now i want that when the year change from 2011 to 2012 my LAB_Id automatically be start from 0 but like this 12000001, 12000002, 12000003.............so on and in 2013 it should be start like 13000001, 13000002,13000003............. this means that first two digit represent the year and with every year changing it should be automatically change.
is it possible if so help me with the help of code

推荐答案

删除列&的身份规范来帮助我?计算和自己插入值

您可以使用
获得最高ID

Remove the identity spec for the column & calculate & insert the value yourself

You can get the highest Id using

Select MAX(Lab_Id) from patient

您可以增加&插入新值

如果年份已更改，请计算新的ID，例如

You can increment & insert the new value

If the year has changed, calculate the new id like

int Id = ((DateTime.Today.Year % 100) * 1000000) + 1

=========================================

另外，您仍然可以使用自动增量和选择在年份更改后暂时关闭自动递增并插入值.

您可以使用

============================================

Alternatively, you can still use auto increment & choose to temporarily turn off auto increment and insert value when the year has changed.

You can use

SET IDENTITY_INSERT patient ON

之后，您可以插入自定义ID值并使用
关闭

After this, you can insert custom id value and turn off using

SET IDENTITY_INSERT patient OFF

请记住，IDENTITY_INSERT一次只能打开一个表.

Remember that IDENTITY_INSERT can be turned on for only one table at a time.

我通常避免在主键/ID字段中放入任何看起来像有意义的数据的东西.通常也最好为每个数据项(而不是任何形式的组合)具有单独的字段，然后添加一个ID字段，其唯一目的是唯一地标识每个记录(主键和用于在表之间链接记录的字段)处于一对多关系中-可以是您喜欢的任何独特的gobbledegook，因为它可以自动生成，并且除了调试等功能外，人类永远不会看到它.

就您而言，我有:
栏位1-ID-自动递增整数或(我的偏好)Guid
栏位2-年
栏位3-病人编号

只是一个想法 . . .

I generally avoid putting anything that looks like meaningful data in a primary key / ID field. It''s also generally preferable to have separate fields for each item of data (rather than any form of composite) and then add an ID field whose sole purpose is to uniquely identify each record (primary key and field used to link records between tables in 1-many relationships - and can be any unique gobbledegook you like as it can be automatically generated and, apart from debugging etc., is never seen by a human).

In your case I''d have:
Field 1 - ID - autoincrement integer or (my preference) a Guid
Field 2 - Year
Field 3 - Patient no.

Just a thought . . .

这篇关于SQL Server 2005自动更改入门密钥的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！