在SQL Express 2005 Server数据库表的int主键和varchar()类型字段中保留相同的数据. [英] Keeping identical data in int primary key and a varchar() type fields of SQL Express 2005 Server database table.

问题描述

在数据库表中，我们有一个varchar()类型字段，出于特定目的，该字段必须维护与int类型主键相同的数据.并且varchar字段将在保存新记录之前计算数据值.

我们使用Select Max(主键列)来检索主字段的最大值，并为此添加1.但是在删除最后一条记录时，新添加的记录的varchar()字段存储已删除记录的值，并且主键字段的值变为+1.

In a database table we have a varchar() type field which has to maintain identical data to int type primary key for a specific purpose. And varchar fields data value to be calculated before saving new records.

We used Select Max(primary key column) to retrieve maximum value of primary field and add 1 to it for this purpose. But on deleting last record, newly added record''s varchar() field stores deleted record''s value and primary key field value becomes +1 to it.

推荐答案

似乎您正在跟踪插入表中的行数.以后是否删除它们都没关系.我认为您应该去审核表.每当发生插入/更新/删除时，触发器都可以更改此表.以后，您可以将其用于所需的任何报告工作.尽管正如人们所说的那样，触发器是邪恶的，但仍有一些方法可以做到这一点:

更改varchar列的数据类型.它应该是整数.并创建一个autoicnrement列.这应该达到目的.尽管您将必须检查行删除对增量值的影响.我相信它应该与Oracle中的序列相同，因此应该更改.

It seems you are keeping track of the number of rows that were inserted in the table. It does not matter whether they were deleted later on. I think you should be going for Audit tables. Whenever Insert/Update/Delete happens, a trigger can change this table. Later you can use this for any reporting job that you need. Although as people say that triggers are evil, there are to ways of doing this:

Change the datatype for the varchar column. It should be integer. And make a autoicnrement column. This should serve the purpose. Alhtough you will have to check the effect of row deletion on the incremental value. I believe it should be same as sequence in Oracle so it should change.

这篇关于在SQL Express 2005 Server数据库表的int主键和varchar()类型字段中保留相同的数据.的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！