Can one table have two identity columns in SQL Server?

Question

I am trying to make two columns auto increment but this column shows an error [user_id] as id + 0 PRIMARY KEY NOT NULL saying

Only UNIQUE or PRIMARY KEY constraints can be created on computed columns

What I am trying to do is, if id = 1, make user_id= 1 as well.

CREATE TABLE [dbo.TBL_TXN_USER]
(
    [id]                int  NOT NULL IDENTITY(1,1),
    [user_id] as id + 0 PRIMARY KEY NOT NULL ,
    [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,  
)

What is wrong in [user_id]? How to solve it?

Answer 1

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.