I have a table with 2 primary keys (int, int) and was wondering if it was possible to have one of the primary keys set to auto increment and the other not? The following is the basics of the table structure:
Table
{
Id - Int,
VersionId - Int
}
Basically I have an Id with a Version so that there is a complete hi开发者_开发技巧story of that record and therefor can be rolled back at any time.
I want to be able to insert and Id and for it to then automatically handle the version number for me. Is this possible?
Thanks in advance.
No, SQL Server has no such "partitioned" identity concept. If you really need this (really??) then you have to provide this programmatically, somehow (from your client, or by handling it yourself in SQL Server using a helper table or something like that).
You can have an IDENTITY
field - but that's a consecutive INT number over your whole table - not consecutive for each Id
. On the other hand, since that IDENTITY will be unique and ever-increasing, you can use that as a version field, too (it's not going to be 1, 2, 3 for each ID - but the sequence is still there).
In that case, you might have
ID Version
1 1
2 2
3 3
1 4
and so forth.
You can create the compound PK but the version incrementing won't work as you expect - it will be continuous and not 'reset' to version 1 on every new ID
create table SomeTable
(
id int not null,
version int identity(1,1) not null,
primary key(id, version)
)
insert into SomeTable(id) values (1234)
insert into SomeTable(id) values (1235)
insert into SomeTable(id) values (1234)
select * from SomeTable
Returns
1234 1
1235 2
1234 3
etc
精彩评论