Saif
Saif

Reputation: 2679

Can one table have two identity columns in SQL Server?

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?

Upvotes: 3

Views: 3378

Answers (3)

Paolo
Paolo

Reputation: 2254

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.

Upvotes: 1

Steven Na
Steven Na

Reputation: 19

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
)

Upvotes: 1

Ramanlfc
Ramanlfc

Reputation: 8354

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.

Upvotes: 0

Related Questions