Creating A Unique Constraint

I forgot all about creating UNIQUE constraints.  Of course I would have a need for it, and you know it goes into the eBrain.

Basically there are two ways to create a UNIQUE constraint.  You can do it via TSQL, or using the SQL Server Management Studio (SSMS).

TSQL


ALTER TABLE tblMyTableName
ADD CONSTRAINT MyConstraintName
UNIQUE(myColumnName);

Pretty simple there eh?  tblMyTableName is the name of your table, MYConstraintName is what you want to call this constraint.  myColumnName is the column or field I want to make UNIQUE.

SSMS

The next part is how to add it with SSMS.  This isn’t too hard either.  In SSMS go to the database where you want this constraint.   Right click the table where you want this constraint.  Click Design.

In the designer go to the top and click Manage Indexes and Keys, it is a little icon that looks like a key on top of a table.

In this new window click the Add button.  In the General section you will need to select the Column you want UNIQUE.  For the Type pick Unique Key.  Of course you will need to give it a name.

Remember, a UNIQUE column can still hold a NULL, but only one record can have that NULL value so that NULL is unique.

Advertisements

About SheldonS

Web developer for over 15 years mainly with Microsoft technologies from classic ASP to .NET 4. Husband, father, and aspiring amateur photographer.

Posted on June 26, 2012, in SQL and tagged , . Bookmark the permalink. 1 Comment.

  1. You are quite correct. Thank you very much for your input. I have to admit, for most tables I usually only have one primary key field. My friends who do a lot of BI Reporting do make use of multiple indexes.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: