Can a SQL Server Table Have Two Identity Columns

Can a sql server table have two identity columns?

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.

Can one table have two identity columns in SQL Server?

the error message is because you put the NOT NULL constraint on the computed column.

on sql server 2012 the complete error message is:

Only UNIQUE or PRIMARY KEY constraints can be created on computed
columns, while CHECK, FOREIGN KEY, and NOT NULL constraints require
that computed columns be persisted.

here is a working script (i changed the table name):

CREATE TABLE dbo.[TBL_TXN_USER]
(
    [id]                int  NOT NULL IDENTITY(1,1),
    [user_id]           as id + 0 persisted not null primary key,
    [username]          varchar(150)    NOT NULL,
    [fullname]          varchar(150)    NOT NUll,
    [pwd]               varchar(50)     NOT NUll,
    [email]             varchar(150)    NOT NULL,
    [mobile]            varchar(150)    NOT NULL,
    [designation]       varchar(150)    NOT NULL,
    [deleted]           int             NULL,
    [created_date]      datetime        NULL,
    [creator_user_id]   int             NULL,
    [changed_date]      datetime        NULL,
    [changer_user_id]   int             NULL,
    [add_content]       int              NULL,
    [edit_content]      int              NULL,
    [delete_content]    int              NULL,
    [manage_user]       int              NULL,
    [view_log]          int              NULL,  
);
GO

i have a couple of comments about that question .

- a calculated field with a fixed formula with static values as primary key instead of the id itself is a waste of resources: one of the 2 fields should not be there

- a field with the name of a system function (user_id) is something i would avoid at all costs.

- the question looks like an attempt to put in place a solution (the calculated field as id) for an hidden issue.

Two identity columns in SQL Server 2008

If you plan on incrementing both columns always at the same time, then one workaround here might be to just use a single auto increment column, but use the remainder of that counter divided by 20 for the second value:

CREATE TABLE tblPersons (
    IDCol int PRIMARY KEY IDENTITY(1,1),
    Name nvarchar(50)
)

SELECT
    IDCol,
    IDCol % 20 AS SortCol    -- this "resets" to zero upon reaching 20
FROM tblPersons;

Problem in creating two identity columns in a single table

In SQL Server, you can have a computed column which just holds the same value as an identity (or any other) column:

CREATE TABLE [dbo].[test](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [idCopy]  AS ([id]),
    [whatever] [nvarchar](max) NOT NULL
)

There can only be one IDENTITY column per table - Why?

An Identity Column in SQL Server has a seed and an auto increment. We could always calculate what the 2nd hypothetical id value should be if we knew the value of the first id column anyway.

e.g. If this was legal syntax

create table #foo
(
bar int identity(1,10),
baz int identity(1000,1)
)

We wouldn't need to store baz as it could be calculated from bar as follows.

baz = 1000 + (bar-1)/10

Can I make an identity field span multiple tables in SQL Server?

The short answer is: No, you can't do that (at least in MS SQL Server through 2008).

You could make a new table, "CommentableEntity", plug your identity column in there, then define foreign keys in Authors and Books to reference it as a parent table, and then do one of a number of tricks to ensure that a given ID value is not assigned to both tables... but this is a poor idea, because the data model you built would imply that Authors and Books are related kinds of data, and they really aren't.

You could have a separate table, Comments, have the identity column in there, and park a CommentId column in both Authors and Books. However, that would limit each book and author to only one comment.

Me, I'd probably add a column like "CommentorType" to the Comments table and a put a flag in there indicating source of comment ("A" for author, "B" for book). Build a primary key on "CommentorId + CommentorType", and it should work well enough -- and it'd be trivial to add further types of commentors as the system expands.

SQL Server can we same give identity colum for two or more tables

For that you have to manage yourself. Because SQL Server does not know about your requirement that, if one table ended with 1001 and start a new identity on another table from 1002. SQL Server Identity syntax is IDENTITY [ ( seed , increment ) ] Which means, each time when you insert an new row it see an Last Seeeed and the Increment it by the value you set up earlier while designing your table.

So, if you want to do so then,
Insert all the rows from Excel to SQL Server. Then, Before starting to insert the rows in Next table, Get the Last Seeed value of previous table. Then, you can use that value to insert.

But this is very Bad Solution you know In my case what i would do is.
I just setup an Counter Variable in my application which take care of the Row Position, and i'd simply insert that Value in the Required Tables.

Anyway, you want to do that then, You can Remove that Identity Property at all. Because you self managing so.

I suggest the following way :

Int RowNum=0
Foreach (Excel doc){
   Foreach (Rows in doc)
     {
        RowNum++;
        Fields1=val ...n
        If data is something 
           {
               Insert into Table 1 values(RowNum,Fields...n)
           }

        If data is something else
           {
               Insert into Table 2 values(RowNum,Fields...n)
           }
     }
}

I think that way you can manage your soultion.

This is my suggestion only with Scratch example. Hope you understand why im suggesting you that way.

Entity Framework 5 Multiple identity columns specified for table. Only one identity column per table is allowed

I encountered the same error when trying to rename a Key column. To make the migration work, I had to reorder the order of operations in my scaffolded migration script.

Here, I made sure to order the Drop operations first, then added the new Key field afterwards.

public partial class RenameKey : DbMigration
{
    public override void Up()
    {
        DropPrimaryKey("dbo.GameSummary", new[] { "OldId" });
        DropColumn("dbo.GameSummary", "OldId");
        AddColumn("dbo.GameSummary", "Id", c => c.Int(nullable: false, identity: true));
        AddPrimaryKey("dbo.GameSummary", "Id");
    }

Hope that helps with your case.

How to set two columns in SQL Server database as auto increment int with increment seed 100?

You can only have one identity column per table, however, there are some ideas and workarounds here

Simulation using a Derived Computed Column

If both "identity" columns are synchronized with each other, or the second identity can be derived from the first using a formula, then a Computed Column might be applicable, e.g. if the second identity is offset by a constant from the actual Identity column:

ALTER TABLE MyTable ADD OtherIdentity AS RealIdentity + 100;

Where RealIdentity is the actual / original IDENTITY column.

Computed Column derived off Identity SqlFiddle example here

Using an Independent Sequence

Another alternative would be to use an independent Sequence (Sql2012 and Later)

CREATE SEQUENCE MySequence START WITH 100;

CREATE TABLE MyTable
(
    RealIdentity INT IDENTITY(1,1),
    RandomCol NVARCHAR(100),
    FakeIdentity INT DEFAULT NEXT VALUE FOR MySequence
);

Sequence SqlFiddle example here

---

Related Topics