More than one unique key in a table [duplicate]

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

Answer 1

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"