Search code examples
sqlsql-serversql-server-2008alter-table

Error when ALTER TABLE - Msg 102, Level 15, State 1

I'm using SQL Server and trying to change an existing table:

ALTER TABLE [Users] 
(
    [User_ID] INT IDENTITY(1,1) PRIMARY KEY,
    [UserName] [NVARCHAR](30) NOT NULL UNIQUE, -- the only change is "UNIQUE"
    [UserEmail] [NVARCHAR](30) NOT NULL UNIQUE,  -- the only change is "UNIQUE"
    [Password] [NVARCHAR](30) NOT NULL,
)

And get this error:

Msg 102, Level 15, State 1, Line 10
Incorrect syntax near '('.

I'm changing the syntax each time but still get this error

Msg 102, Level 15, State 1

What is the problem ?

Solution

  • Cannot add Unique Key constraint at column level. Unique Key Constraint are defined at table level.

    You may alter table to add Unique key in below ways.

    ALTER TABLE table_name
ADD CONSTRAINT constraint_name UNIQUE (column1, column2, ... column_n);

    for e.g.

    ALTER TABLE Users
ADD CONSTRAINT Users_Unique UNIQUE (UserName, UserEmail);