如何自动增加数据库字段 [英] How to Auto increment a database field
问题描述
我的项目包含一个数据库表'DetailsTB'。和乘客记录相对于它的AutoNo，ReceiptNo保存。
AutoNo。 column是一个带bigint的数据类型。
AutoNo是一个自动增量列。
AutoNo在2013年以1开头。而ReceiptNo将其设为12013。
并且在2013年底，ReceiptNo达到55702013.
ReceiptNo是AutoNo和当前年份的组合。
问题是当2014年开始时，收据编号以55712014开头而不是12014.意味着只有AutoNo必须在2014年开始时以1开头。
我想要，当2014年开始，收据号码不以55712014开头，必须从12014开始。意味着AutoNo在2013年底停止到5570，同一列AutoNo从2014年开始时以1开始。
当前一年开始时，第一个ReceiptNo是12013，而当新的一年2014年开始时，第一个ReceiptNo自动为12014.因此，ReceiptNo 12013和12014之间相对于年份很容易区分和识别。
为什么我这样做？
因为，如果我在新年开始时没有将AutoNo设为1，它仍然继续......但是在某一年之后它会达到无数（无限）数字所以必须使它成为1新的一年开始的时候。
如何做到这一点。
我的自动递增代码：
con.Open（）;
cmd = new SqlCommand（ SELECT IsNull（MAX（ReceiptNo），0）+1 FROM DetailsTB，con）;
SqlDataReader sdr = cmd.ExecuteReader（）;
while（sdr.Read （））
{
txt_haj_receiptno.Text = sdr [0] .ToString（）;
}
con.Close（）;
提前谢谢。
My Project contains a database table 'DetailsTB'. and passenger record are save with respect to it's AutoNo, ReceiptNo.
AutoNo. column is an data type with bigint.
AutoNo is an auto increment column.
The AutoNo start with 1 in the year 2013. and the ReceiptNo make it as 12013.
and at the end of year 2013 the ReceiptNo reaches to 55702013.
The ReceiptNo is a combination of AutoNo and the current year.
The problem is when the year 2014 start, the Receipt No. start with 55712014. instead of 12014. Means only AutoNo must start with 1 when 2014 start.
I want, when the 2014 start, the Receipt No. not start with 55712014 it must start with 12014. means AutoNo stop to 5570 at end of year 2013 and same column AutoNo start with 1 when 2014 start.
when previous year start the first ReceiptNo is 12013 and when new year 2014 start the first ReceiptNo is automatically 12014. so there is easily differentiation and identifiable between ReceiptNo 12013 and 12014 with respect to year.
why I am doing this ?
Because, if I am not make AutoNo to 1 when new year start it's still continued...but at certain year later it reaches to uncountable(infinite) numbers so the necessary to make it as 1 when new year start.
How to do that same.
My code for auto increment:
con.Open();
cmd = new SqlCommand("SELECT IsNull(MAX(ReceiptNo),0)+1 FROM DetailsTB", con);
SqlDataReader sdr = cmd.ExecuteReader();
while (sdr.Read())
{
txt_haj_receiptno.Text = sdr[0].ToString();
}
con.Close();
Thanks in advance.
推荐答案
如果你有收据号主键然后你不能。如果你对两个记录使用相同的主键，这会破坏数据库中的关系数据。
如果这是你真正想做的事情，我会建议使用每年不同的表，或添加另一个可以自动递增的RecordNo主键。如果你这样做，你必须手动自动增加每个记录插入（最好用存储过程执行此操作）。
出于好奇，为什么要为两个不同的收据设置相同的收据号码？想想这里的增长情况，当您使用数字5700结束2013年后会发生什么，然后在1月31日您发布产品XYZ，在一天内获得5700个订单（会很好）。你必须在相隔一天的时间内收到相同数量的收据......我知道这种情况不太可能，但如果它们永远增量又有什么关系？
If you have Receipt No as the primary key then you can't. This would break relational data in the database if you use the same primary key for two records.
If this is something you really want to do, I would suggest using a different table for each year, or adding another primary key like RecordNo that can auto-increment. If you do it that way, you would have to manually auto-increment each record as its inserted (best to do this with a stored procedure).
Out of curiosity, why would you want to have the same receipt number for two different receipts? Think of growth here, what happens when you do end 2013 with number 5700 and then on January 31 you release product XYZ that gets 5700 orders in one day (would be nice). You'd have to receipts with the same number just one day apart... An unlikely scenario I know, but what does it matter if they increment forever?
我建议使用序列代替。自动增量/标识字段不值得他们造成的麻烦;总是有更好的方法。
I recommend using a sequence instead. Auto-increment/identity fields aren't worth the trouble they cause; there's always a better way.
表中的字段不能包含相同的ID，例如在2013年，已经有一个ID 1，并且在2014年不能有ID 1，如果你想这样做，那么你必须制作一个复合主键，其中组成将是Year字段和ID字段。
A filed in a Table cannot contain same ID e.g. in 2013 there is already an ID 1, and in 2014 there cannot be an ID 1, if you want to do it, then you must make a composite primary key where the composition will be Year field and ID field.
这篇关于如何自动增加数据库字段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
