Can a sql server table have two identity columns?

Question

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.

Wertz

What version of SQL Server are you using? – War

Answer 1

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.

Benevolent

I tried this but often a setting can be changed which is why I asked the question. – Wertz

It's by spec. See the added link to msdn. – Benevolent

Answer 2

You can use Sequence for second column with default value IF you use SQL Server 2012

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

http://technet.microsoft.com/en-us/library/ff878058.aspx

Kharkov

Good suggestion, only problem is Truncation of the Table. – Morelos

I'm not sure that Truncation is a problem, if you want to reset sequence, you can easily do that – Kharkov

Yes but unlike the Identity identifier, you have to either drop the sequence and then re-add or go through and redefine the start for the sequence. For development and/or testing, Sequence is not an ideal implementation. Once you get past the dev/testing stage, it is a massively useful implementation. Just a PITA during the dev-stages – Morelos

Answer 3

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

Existence

Answer 4

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

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.

Amateurism

Answer 5

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.

Calla

Answer 6

The primary key doesn't need to be an identity column.

You can't have two Identity columns.

You could get something close to what you want with a trigger...

War

This is problematic. A primary key does not allow null values. Usually auto-generated keys are left out on inserts. Even if you use a trigger with INSTEAD OF INSERT to generate your primary key it will puke (Tested on SQLSEVER2000) – Polyphemus

@snmcdonald: Don't use SQL Server 2000 in 2011 year. – Penthouse

Answer 7

in sql server it's not possible to have more than one column as identity.

Paries

Answer 8

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,

Coronary

Answer 9

In most relational databases like SQL Server, Oracle, or MySQL, a table can typically have only one identity column. An identity column is a column that automatically generates unique values when new rows are added to the table.

Having multiple identity columns within a table would create ambiguity regarding which column serves as the primary key. However, you can achieve similar functionality by using other approaches, such as:

1. Composite Keys:

CREATE TABLE Orders (
    OrderID INT IDENTITY(1,1),
    ProductID INT,
    Quantity INT,
    -- Other columns related to orders

    CONSTRAINT PK_Orders PRIMARY KEY (OrderID, ProductID)
);

Rabon

Answer 10

A workaround would be to create an INSERT Trigger that increments a counter.

So I have a table that has one identity col : applicationstatusid. its also the primary key. I want to auto increment another col: 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

Scotopia

CREATE Sequence below allows you to make a default value that auto-increments. Before 2012 you could use a function that increments a value in a table and returns it (similar to what you have done in your answer). And that could be a default value for a column. Triggers should be avoided. – Hyson