Hi,
How to add unique constraint on varchar field. For Ex in my table company name should be unique.. I dont want the user to enter duplicate values
Loading
Know the answer? Post it — somebody with the same question will find it here.
Sign in to answer this question
It is the same account you read, post and publish with — and you will come straight back to this page.
Pravin MorePosted Nov 25, 2011, 6:27 AM
You cannot put a unique constraint on a VARCHAR(MAX) column (which could be up to 2 GB of text!!).
320 chars is the max length.
Thanks,
Pravin.
Priya LingePosted Nov 25, 2011, 6:21 AM
CREATE TABLE Persons
(
P_Id int,
CompanyName varchar(255),
CONSTRAINT Companyname_person UNIQUE(CompanyName))
select * from Persons
insert into Persons values(1,'XYZ')
insert into Persons values(2,'XYZ')----At this stage it gives the message
Msg 2627, Level 14, State 1, Line 2
Violation of UNIQUE KEY constraint 'Comapnyname_person'. Cannot insert duplicate key in object 'dbo.Persons'.
The statement has been terminated.
Hope this will help you.
Thanks.