The operation failed because an index or statistics with name 'XPKUSERS'

Question

Need help in understanding the error "The operation failed because an index or statistics with name 'XPKUSERS'" and help in converting the query to TSQL

CREATE TABLE TDM_USERS_AUTH 
(
  USER_ROLE_ID VARCHAR(3) NOT NULL 
, ROLE VARCHAR(100) NOT NULL 
, USER_ID VARCHAR(15) NOT NULL 
);
CREATE UNIQUE INDEX XPKUSERS ON TDM_USERS_AUTH (USER_ROLE_ID ASC);
ALTER TABLE TDM_USERS_AUTH
ADD CONSTRAINT XPKUSERS PRIMARY KEY 
(
  USER_ROLE_ID 
)
USING INDEX XPKUSERS;

Msg 1913, Level 16, State 1, Line 1
The operation failed because an index or statistics with name 'XPKUSERS' already exists on table 'TDM_USERS_AUTH'.
Msg 1750, Level 16, State 0, Line 1
Could not create constraint or index. See previous errors.

Answer 1

The USING INDEX clause is a syntax specific to Oracle. In SQL Server, a new index is always created when you create a primary key. You can only choose if the new index is clustered (which is by default) or non-clustered.

You can simply create the PRIMARY KEY (without creating the index before), like this:

CREATE TABLE TDM_USERS_AUTH 
(
  USER_ROLE_ID VARCHAR(3) NOT NULL 
, ROLE VARCHAR(100) NOT NULL 
, USER_ID VARCHAR(15) NOT NULL 
);
ALTER TABLE TDM_USERS_AUTH
ADD CONSTRAINT XPKUSERS PRIMARY KEY 
(
  USER_ROLE_ID 
);

You can also specify the primary key when creating the table:

CREATE TABLE TDM_USERS_AUTH 
(
  USER_ROLE_ID VARCHAR(3) NOT NULL CONSTRAINT XPKUSERS PRIMARY KEY 
, ROLE VARCHAR(100) NOT NULL 
, USER_ID VARCHAR(15) NOT NULL 
);