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.
Only one identity column can be created per 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.
Any column in a table can be an identity column, but there can only be one identity column per table.
Each table can have only one column with the IDENTITY property.
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With