How to restric the duplicate value on nvarchar column in SQL express 2008

Question

0.00/5 (No votes)

See more:

SQL-Server-2008R2

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 19-Aug-16 23:59pm

basitsar

Updated 20-Aug-16 0:23am

OriginalGriff

v2

Add a Solution

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'.

2 solutions

Solution 1

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.

Posted 20-Aug-16 0:09am

OriginalGriff

Comments

basitsar 20-Aug-16 6:23am

not working showing error
Incorrect syntax near the keyword 'OFF'.

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Karthik_Mahalingam · Accepted Answer · 2016-08-20T00:23:00

Solution 2

refer SQL UNIQUE Constraint[^]

SQL

ALTER TABLE [User]
ADD CONSTRAINT uc_User_Name UNIQUE (User_Name)

Posted 20-Aug-16 0:23am

Karthik_Mahalingam

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

Karthik_Mahalingam 20-Aug-16 7:37am

welcome