Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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.

like image 291
William Hurst Avatar asked Dec 08 '08 10:12

William Hurst


People also ask

Can we have 2 identity columns in a table?

Only one identity column can be created per table.

How many identity can you have in a table?

Technically a table can have only one identity column and if you try to add another identity column to it, it will give you an error.

How many columns can be defined as identity column?

Any column in a table can be an identity column, but there can only be one identity column per table.

How many columns with an identity property are supported in one table?

Each table can have only one column with the IDENTITY property.


1 Answers

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

Only one identity column can be created per table.

like image 99
Eugene Yokota Avatar answered Oct 09 '22 16:10

Eugene Yokota