William Hurst William Hurst - 3 years ago 105
SQL Question

Can a sql server table have two identity columns?

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.

Answer Source
CREATE TABLE [dbo].[Foo](
	[FooId] [int] IDENTITY(1,1) NOT NULL,
	[BarId] [int] IDENTITY(1,1) NOT NULL


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):

Only one identity column can be created per table.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download