一个 sql server 表可以有两个标识列吗？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/349092/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Can a sql server table have two identity columns?
提问by William Hurst
I need to have one column as the primary key and another to auto increment an order number field. Is this possible?
我需要将一列作为主键，另一列用于自动增加订单号字段。这可能吗？
EDIT: I think I'll just use a composite number as the order number. Thanks anyways.
编辑：我想我只会使用一个复合数作为订单号。不管怎么说，多谢拉。
回答by Eugene Yokota
CREATE TABLE [dbo].[Foo](
[FooId] [int] IDENTITY(1,1) NOT NULL,
[BarId] [int] IDENTITY(1,1) NOT NULL
)
returns
返回
Msg 2744, Level 16, State 2, Line 1
Multiple identity columns specified for table 'Foo'. Only one identity column per table is allowed.
So, no, you can't have two identity columns. You can of course make the primary key not auto increment (identity).
所以，不，你不能有两个标识列。您当然可以使主键不自动递增（身份）。
Edit: msdn:CREATE TABLE (Transact-SQL)and CREATE TABLE (SQL Server 2000):
编辑：msdn:CREATE TABLE (Transact-SQL)和CREATE TABLE (SQL Server 2000)：
Only one identity column can be created per table.
每个表只能创建一个标识列。
回答by benkevich
You can use Sequence for second column with default value IF you use SQL Server 2012
如果您使用 SQL Server 2012，则可以将 Sequence 用于具有默认值的第二列
--Create the Test schema
CREATE SCHEMA Test ;
GO
-- Create a sequence
CREATE SEQUENCE Test.SORT_ID_seq
START WITH 1
INCREMENT BY 1 ;
GO
-- Create a table
CREATE TABLE Test.Foo
(PK_ID int IDENTITY (1,1) PRIMARY KEY,
SORT_ID int not null DEFAULT (NEXT VALUE FOR Test.SORT_ID_seq));
GO
INSERT INTO Test.Foo VALUES ( DEFAULT )
INSERT INTO Test.Foo VALUES ( DEFAULT )
INSERT INTO Test.Foo VALUES ( DEFAULT )
SELECT * FROM Test.Foo
-- Cleanup
--DROP TABLE Test.Foo
--DROP SEQUENCE Test.SORT_ID_seq
--DROP SCHEMA Test
回答by Simon Powers
Add one identity column and then add a computed column whose formula is the name of the identity column
添加一个标识列，然后添加一个计算列，其公式为标识列的名称
Now both will increment at the same time
现在两者将同时增加
回答by Only333
No it is not possible to have more than one identity column.
不，不可能有多个标识列。
The Enterprise Manager does not even allow you to set > 1 column as identity. When a second column is made identity
企业管理器甚至不允许您将 > 1 列设置为身份。当第二列成为身份时
Also note that @@identity returns the last identity value for the open connection which would be meaningless if more than one identity column was possible for a table.
另请注意，@@identity 返回打开连接的最后一个标识值，如果一个表可能有多个标识列，则该值将毫无意义。
回答by Ali Karaca
create table #tblStudent
(
ID int primary key identity(1,1),
Number UNIQUEIDENTIFIER DEFAULT NEWID(),
Name nvarchar(50)
)
Two identity column is not possible but if you accept to use a unique identifier column then this code does the same job as well. And also you need an extra column - Name column- for inserting values.
两个身份列是不可能的，但如果您接受使用唯一标识符列，那么此代码也可以完成相同的工作。而且您还需要一个额外的列 - 名称列 - 用于插入值。
Example usage:
用法示例：
insert into #tblStudent(Name) values('Ali')
select * from #tblStudent
Ps: NewID()function creates a unique value of type uniqueidentifier.
Ps：NewID()函数创建一个类型为 uniqueidentifier 的唯一值。
回答by Joe Ratzer
The primary key doesn't need to be an identity column.
主键不需要是标识列。
You can't have two Identity columns.
您不能有两个 Identity 列。
You could get something close to what you want with a trigger...
你可以用触发器得到接近你想要的东西......
回答by NiL
I've just created a code that will allow you inserting two identities on the same table. let me share it with you in case it helps:
我刚刚创建了一个代码，允许您在同一个表中插入两个身份。如果有帮助，让我与您分享：
create trigger UpdateSecondTableIdentity
On TableName For INSERT
as
update TableName
set SecondIdentityColumn = 1000000+@@IDENTITY
where ForstId = @@IDENTITY;
Thanks,
谢谢，
回答by Mladen Prajdic
in sql server it's not possible to have more than one column as identity.
在 sql server 中，不可能有超过一列作为标识。
回答by Neha Verma
A workaround would be to create an INSERT Trigger that increments a counter.
一种解决方法是创建一个增加计数器的 INSERT 触发器。
So I have a table that has one identity col : applicationstatusid. its also the primary key. I want to auto increment another col: applicationnumber
所以我有一个有一个身份列的表：applicationstatusid。它也是主键。我想自动增加另一个列：applicationnumber
So this is the trigger I write.
所以这是我写的触发器。
create trigger [applicationstatus_insert] on [ApplicationStatus] after insert as
update [Applicationstatus]
set [Applicationstatus].applicationnumber =(applicationstatusid+ 4000000)
from [Applicationstatus]
inner join inserted on [applicationstatus].applicationstatusid = inserted.applicationstatusid
