SQL Server More than one unique key in a table

zed5wv10 于 2023-10-15 发布在其他

关注(0)|答案(2)|浏览(102)

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

ProductId is primary key. I need both ProductCode and ProductStamp unique separately (not combined). For example:

Allowed

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

Not allowed

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

How do I achieve this?

sql-server

来源：https://stackoverflow.com/questions/30705943/more-than-one-unique-key-in-a-table

2条答案

按热度按时间

v64noz0r1#

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"

赞(0）回复(0）举报 2023-10-15

zour9fqk2#

You need to add a Unique Index:

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

) WITH IGNORE_DUP_KEY;

赞(0）回复(0）举报 2023-10-15

我来回答

SQL Server More than one unique key in a table

2条答案

相关问题

热门标签

最新问答