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.
Advertisement
Answer
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 );