sql server：唯一约束生成唯一键和唯一索引

mark 发表于 Dev

路德

我有2张桌子：

attCatAppSet，attCatAppSet_translation

在两个表上，我都对2列（不是主键）应用了唯一约束，以使列对值不能重复。

GO
ALTER TABLE attCatAppSet
ADD CONSTRAINT UQ_category_id_setOrder 
UNIQUE(category_id, setOrder)
GO


GO
ALTER TABLE attCatAppSet_translation
ADD CONSTRAINT UQ_siteLanguage_id_attCatAppSet_id 
UNIQUE(siteLanguage_id, attCatAppSet_id)
GO

结果：查看对象资源管理器，我得到了命令的2种不同实现。在表attCatAppSet上，有一个唯一的索引约束。在表attCatAppSet_translation上，有一个唯一的索引和唯一的键约束。

在此处输入图片说明

如果我打电话，同样的事情表明：

GO
sp_helpIndex attCatAppSet
GO
sp_helpIndex attCatAppSet_translation

在此处输入图片说明

为什么会有两种不同的查询实现？
2个结果之间有什么区别？

Vojtech多纳尔

Your constraint in the table attCatAppSet contains setOrder field, that is not a foreign key. So your foreign key in attCatAppSet is just category_id - and it is also displayed as a foreign key. But your constraint UNIQUE(category_id, setOrder) requires combined unique index of category_id and setOrder - and so this index was created and is displayed as a normal index (not as a foreign key).

Foreign keys are used to define relations between tables. It seems that you have already created the relations before creating theese constraints. (e.g. you have already defined relation between attCatAppSet_translation and siteLanguage tables etc.)

The constraint attCatAppSet_translation contains two foreign keys, so it is displayed as a key that has following meaning: attCatAppSet_translation table contains only unique combinations of siteLanguage and attCatAppSet.

Only two combined unique indexes were created. The both indexes created by SQL Server are functionally equivalent, only they are displayed with different icons. It is only about how database model is documented within SQL Server Management Studio.

Further information here and here.

UNIQUE constraints are part of the ANSI SQL definition and defining UNIQUE constraints is part of defining a database's logical design.

从性能的角度来看，UNIQUE约束和唯一索引与查询优化器实际上是相同的，并且您不会发现使用一个与另一个相比对性能有任何好处。

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。

编辑于2021-02-14

我来说两句

0条评论

登录后参与评论

上一篇：SqlCommand.ExecuteReader使用带输出参数的存储过程不返回任何行

来自分类Dev

Related 相关文章

文章