Click here to Skip to main content
15,904,023 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
Hi,

How to restric the duplicate value on nvarchar column in sql Express 2008

Below is my table

SQL
CREATE TABLE [dbo].[User](
	[User_No] [int] IDENTITY(1,1) NOT NULL,
	[User_Name] [nvarchar](max) NOT NULL
) ON [PRIMARY]

GO


I want to restric if the User_Name is duplicate.

What I have tried:

CREATE UNIQUE INDEX ix_IndexName ON User(User_Name) WITH IGNORE_DUP_KEY = <on>

but got error.
Incorrect syntax near '<'

[edit]HTML encoded[/edit]
Posted
Updated 20-Aug-16 0:23am
v2
Comments
Karthik_Mahalingam 20-Aug-16 6:05am    
did you try constraint ?
basitsar 20-Aug-16 6:13am    
no how to do this
Karthik_Mahalingam 20-Aug-16 6:23am    
check my solution
basitsar 20-Aug-16 6:18am    
CREATE UNIQUE CLUSTERED INDEX UIX_User
ON dbo.[User]([User_Name])

then got error.

Column '[User_Name]' in table 'dbo.User' is of a type that is invalid for use as a key column in an index.
basitsar 20-Aug-16 6:35am    
got error

Incorrect syntax near the keyword 'ADD'.

refer SQL UNIQUE Constraint[^]

SQL
ALTER TABLE [User]
ADD CONSTRAINT uc_User_Name UNIQUE (User_Name)
 
Share this answer
 
Comments
basitsar 20-Aug-16 6:38am    
Got error
Incorrect syntax near the keyword 'ADD'.
Karthik_Mahalingam 20-Aug-16 7:07am    
change the user_name datatype value to lesser number and try
CREATE TABLE [dbo].[User](
[User_No] [int] IDENTITY(1,1) NOT NULL,
[User_Name] [nvarchar](50) NOT NULL
)

ALTER TABLE [User]
ADD CONSTRAINT uc_User_Name UNIQUE ([User_Name])
basitsar 20-Aug-16 7:24am    
It means that while creating table we can create constraint after creating table cannot? Is that correct?
Karthik_Mahalingam 20-Aug-16 7:26am    
no sir.
the issue is with the datatype of user_name column,
you cannot create unique constraint with more than 1000 chars, in your code you have declared as nvarchar(max).
so update the table first and then update the constraint.
basitsar 20-Aug-16 7:35am    
Many Many Thanks
Try removing the < and > symbols:
SQL
CREATE UNIQUE INDEX ix_IndexName ON User(User_Name) WITH IGNORE_DUP_KEY = ON
Or better:
SQL
CREATE UNIQUE INDEX ix_IndexName ON User(User_Name) WITH IGNORE_DUP_KEY = OFF
If you want to disallow duplicates.

But personally, I'd do that as part of my user interface code: when a user tries to register I check to see if the username is taken. If it is, tell them. If it isn't, tell them it's fine.
Waiting until the last minute when it's inserted isn't the "nicest" way for a user.
 
Share this answer
 
Comments
basitsar 20-Aug-16 6:23am    
not working showing error
Incorrect syntax near the keyword 'OFF'.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900