Set column to allow unique values only in SQL 2000 using SQL Management Studio Express?

Alfahane
Alfahane used Ask the Experts™
on
I have several columns that I want to set to have unique values only in SQL 2000. I'm using MS SQL Server Management Studio Express 2005.

I have set the column ID as primary key. But I want to make sure that EMAIL and USERNAME are always unique so that I dont have to rely on doing "manually" checks via a script.

From what I understand I should set EMAIL and USERNAME as unique indexes. Is this correct? If so, how do I do that in Studio Express 2005. If not so, what should I do?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
you can define unique index or can make composite primary key on those both fields.
right click on table->design->select both fields and right click->click on "Set primary key". for more info. look at attached image

ee.JPG
above response was showing you how to add composite PK with SSMS, now here is the code if you don't want to go from management studio.

ALTER TABLE TableName
 ADD CONSTRAINT pk_EmailAndUserName PRIMARY KEY (email,username)
 GO
Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

now if you don't want to make composite PK and want to add unique index, use below code

ALTER TABLE YourTableName ADD CONSTRAINT IX_Email_UserName UNIQUE(Email,UserName)
 GO

Author

Commented:
The pictures shows how to make primary key, right? I already have a primary key ID and try set a primary key on EMAIL and USERNAME then ID will loose it's primary key property.

However, you last comment containing " ... UNIQUE(Email,UserName) ... " seem to be what I am looking for. Do you mean that there is no way of doing it by clicking and selecting? like you showed in your first comment with the primary key?

I'm working with a live database so I try to avoid running SQL queries in case I'd make a mistake in the query
see image here:
sorry image didn't come, let me attache again.

ee.JPG

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial