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?

What? You code doesn't make sense. What is your intention? Having two columns that are the same doesn't really make sense. — Gordon Linoff, Nov 12 '15 at 02:27
id and user_id are not the same, I am using them for different purpose — Saif, Nov 12 '15 at 02:28
for example, id = 1, I want user_id = 1 as well or user_id = 2 by adding one to id — Saif, Nov 12 '15 at 02:30
Which database engine? ANSI SQL doesn't really define auto increment. — Sami Kuhmonen, Nov 12 '15 at 02:34
SQL, one table has only one identity column, however we can use computed column to achieve goal. — Saif, Nov 12 '15 at 02:36
_When_ do you want id = 1, user_id = 2? Do you want to be able to update it afterwards? — Nick.Mc, Nov 12 '15 at 04:55

score 1 · Answer 1 · edited Nov 12 '15 at 07:36

1

Sorry for my misunderstanding, So you want to add auto increments two column in one table. Actually that is not accept at SQL-server so I am going to give you another option below

CREATE TRIGGER [dbo].[insert_triger] ON [dbo].[TBL_TXN_USER]
FOR INSERT
AS
update TBL_TXN_USER set [user_id] = id
where id = (
  select MAX(id)
  from TBL_TXN_USER
)

edited Nov 12 '15 at 07:36

answered Nov 12 '15 at 04:50

Steven Na

19
2

1

This is not going to work properly. You should get the `ID` from the `inserted` "table" that is available in the trigger. – Nov 12 '15 at 07:37
If you want to test it properly, table script must be modified first! Id is primary key with increment setting and user id is null able setting~ – Steven Na Nov 12 '15 at 07:41
This will also fail when inserting more than one row in to the table at one time. *(If the table has two rows with ids `{1, 2}` and then you insert two new rows in a single INSERT statement you'll end up with `{1, 2, NULL, 4}`.)* – MatBailie Nov 12 '15 at 13:55

Paolo · Accepted Answer · 2015-11-12T13:51:59.023

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.

score 0 · Answer 3 · answered Nov 12 '15 at 05:00

column aliases work with select statement not create table, also for [user_id] you didn't provide any data type. Use the following to create your table :

CREATE TABLE [dbo.TBL_TXN_USER](
[id]                int  NOT NULL IDENTITY(1,1),
[user_id]         int PRIMARY KEY NOT NULL  ,
....rest of code

To update [user_id] consider using a trigger.

Can one table have two identity columns in SQL Server?

3 Answers3