More than one unique key in a table

Question

I have a Product table in my DB with following columns :

ProductId INT IDENTITY PRIMARY KEY
ProductCode VARCHAR(100) NOT NULL
ProductStamp VARCHAR(100) NOT NULL

I need both ProductCode and ProductStamp unique for example :

Allowed

Code Stamp
---- -----
A001 S001
A002 S002

Not allowed

Code Stamp
---- -----
A001 S001
A001 S002

How to achieve this? Thank you very much

You can have UNIQUE constraint(s) in addition to PRIMARY KEY. — Branko Dimitrijevic, Jun 08 '15 at 09:50
@TimSchmelter: Code is not a primary key, ProductId is. I just need both code and stamp unique separately (not combined). See my example for details. — tickwave, Jun 08 '15 at 09:53
Your sample data doesnt show that ProductStamp must be unique. Is `A001 S002` allowed in the first sample? Then you need to include both columns in the unique index. — Tim Schmelter, Jun 08 '15 at 09:56

xan · Accepted Answer · 2015-06-09T08:39:16.230

Unique constraint on a single column:

ALTER TABLE Product ADD CONSTRAINT AK_Product_ProductCode UNIQUE( ProductCode )
ALTER TABLE Product ADD CONSTRAINT AK_Product_ProductStamp UNIQUE( ProductStamp )

These will raise an error if there are two rows with duplicate product codes, OR product stamps.

Unique constraint on a multiple columns:

ALTER TABLE Product ADD CONSTRAINT AK_Product_ProductCodeAndStamp UNIQUE( ProductCode, ProductStamp )

This will fire if there are two rows with code AND stamp the same.

The convention "AK" for naming stands for "Alternate Key"

score 1 · Answer 2 · edited Dec 15 '17 at 11:33

1

You need to add a Unique Index:

CREATE UNIQUE NONCLUSTERED INDEX [IX_NAME] ON [your_table] 
(
    ProductCode

) WITH IGNORE_DUP_KEY;

edited Dec 15 '17 at 11:33

Lewis86

511
6
15

answered Jun 08 '15 at 09:52

Galma88

2,398
6
29
50

1

He needs it only for `ProductCode` – Tim Schmelter Jun 08 '15 at 09:53
didn't he say both productcode and productstamp? – Jeremy C. Jun 08 '15 at 09:54
I've corrected my answer – Galma88 Jun 08 '15 at 09:54
1

Reading better the question he only need a unique index on ProductCode – Galma88 Jun 08 '15 at 09:55
The question required ProductStamp to be considered, isn't? – Manohar Reddy Poreddy Apr 08 '18 at 06:39

score 0 · Answer 3 · answered Jun 08 '15 at 09:55

Based on your needs, I would recommend using a unique constraint or unique index to the column(s) you need uniqueness enforced by the database.

Note that a unique constraint is not indexed in any way, so if you have a lot of use for the unique columns in where clauses (or in joins), you will probably want to use an index.
If not, a unique constraint would be better in terms of storage size and insert/update costs.

More than one unique key in a table

3 Answers3