Add a Unique Constraint to a Column - High-Flying SQL Tips


SQL Tips

Add a Unique Constraint to a Column

CURSOR

DELETE

Dirty Reads and Phantom Reads

INSERT

Recursive SQL

ROW_NUMBER() - Automatic Paging

SELECT, FROM

SQL Default Date Format

UPDATE

WHERE

Sponsored Links

73058_New Scooba® 230 Floor Washing Robot + Free Shipping!

 

Add a Unique Constraint to a Column

Here we look at how to add a unique constraint to a database column.

You may have a column in your database for which you want to enforce the values to be unique. You can do this with a primary key, but you may have other columns which aren't your primary key but you still you want to have unique (or distinct) values in the column.

In the example below, we add a Unique constraint to our column called 'myColumnName', which will then ensure that only unqiue values are allowed in that column.


-- Make the myColumnName column store only unqiue values
ALTER TABLE myTable ADD UNIQUE (myColumnName)

In the second example, shown below, we make the 'myColumnName' and 'mySecondColumnName' columns combined together store unique values. That is, when the value of 'myColumnName' is combined with the value of 'mySecondColumnName', the combined value is unique, almost like a Composite key.


-- Make the myColumnName and mySecondColumnName columns combined store only unqiue values
ALTER TABLE myTable ADD UNIQUE (myColumnName, mySecondColumnName)