Unique Column

Question

Unique Column

hoseam

SSCertifiable

Points: 5533
More actions
April 10, 2014 at 3:13 am

#306231

Hi
I have this table:
CREATE TABLE [dbo].[Product](
[ProductID] [int] IDENTITY(1,1) NOT NULL,
[Code] [nvarchar](max) NOT NULL,
[Qty] [int] NULL
)
and I want to ALTER the table:
ALTER TABLE Product
ADD CONSTRAINT ucPrd_Code UNIQUE(Code)
and I get this error:
Msg 1919, Level 16, State 1, Line 1
Column 'Code' in table 'Product' is of a type that is invalid for use as a key column in an index.
Msg 1750, Level 16, State 0, Line 1
Could not create constraint. See previous errors.
How is it that I can't make a varchar type Unique?

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Because you defined it as NVARCHAR(MAX), a character string up to 2 billion characters long. Index keys can be no larger than 900 bytes.

Is your product code really over 4000 characters long?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

hoseam SSCertifiable Points: 5533 More actions · Answer 2

It won't get to billion.

I tried [Code] [nvarchar](10) NOT NULL and I get this error:

Cannot use TEXTIMAGE_ON when a table has no text, ntext, image, varchar(max), nvarchar(max), non-FILESTREAM varbinary(max), xml or large CLR type columns.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

Remove the TEXTIMAGE_ON clause of the create table, as it only has meaning when there is a LOB column in the table

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

hoseam SSCertifiable Points: 5533 More actions · Answer 4

this is how I created my table:

CREATE TABLE [dbo].[Product](

[ProductID] [int] IDENTITY(1,1) NOT NULL,

[Code] [nvarchar](max) NOT NULL,

[Qty] [int] NULL

)

I don't understand what do you mean when you say "Remove the TEXTIMAGE_ON clause of the create table"

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

Great. Now drop the table, convert that to a char(10) (nchar if you need arabic, chinese, etc) and then run modified create table, as you have it above.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass