SQLServer 约束与索引的区别

2023-02-21 00:00:00 索引 创建 约束 禁用 重复

约束和索引在表设计中是常见的，都有属性，用于约束表字段的性。但是，这两者到底有什么区别呢？


接下来我们测试看看，创建两张表分别用于测试约束和索引。


    USE [DemoDB]


    GO


    CREATE TABLE [dbo].[TableUniqueKey](


      id int not null,


      name varchar(20) null


    )


    GO


    CREATE TABLE [dbo].[TableUniqueIndex](


      id int not null,


      name varchar(20) null


    )


    GO


    INSERT INTO [dbo].[TableUniqueKey]


    SELECT 1,'KK' UNION ALL


    SELECT 2,NULL UNION ALL


    SELECT 3,NULL 


    GO


    INSERT INTO [dbo].[TableUniqueIndex]


    SELECT 1,'KK' UNION ALL


    SELECT 2,NULL UNION ALL


    SELECT 3,NULL 


    GO



    现在分别创建键约束和创建索引，发现错误！

      --  创建键约束


      ALTER TABLE [dbo].[TableUniqueKey] ADD CONSTRAINT [IX_TableUniqueKey_name] UNIQUE ([name] ASC)--默认非聚集索引


      GO


      ALTER TABLE [dbo].[TableUniqueKey] ADD CONSTRAINT [IX_TableUniqueKey_name] UNIQUE NONCLUSTERED([name] ASC)


      GO


      --  创建索引


      CREATE UNIQUE NONCLUSTERED INDEX [IX_UniqueIndex_name] ON [dbo].[TableUniqueIndex]([name] ASC)


      GO









      Msg 1505, Level 16, State 1, Line 1
      The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'dbo.TableUniqueKey' and the index name 'IX_TableUniqueKey_name'. The duplicate key value is (<NULL>).
      Msg 1750, Level 16, State 0, Line 1 Could not create constraint. See previous errors.
      The statement has been terminated.

      Msg 1505, Level 16, State 1, Line 1
      The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'dbo.TableUniqueIndex' and the index name 'IX_UniqueIndex_name'. The duplicate key value is (<NULL>).
      The statement has been terminated.


      可以看到，都提示有重复值，重复值均为 NULL 值。现在删除重复值，再创建约束。


        DELETE FROM [dbo].[TableUniqueKey] WHERE ID = 3


        DELETE FROM [dbo].[TableUniqueIndex] WHERE ID = 3


        --  创建键约束


        ALTER TABLE [dbo].[TableUniqueKey] ADD CONSTRAINT [IX_TableUniqueKey_name] UNIQUE NONCLUSTERED([name] ASC)






        创建键约束，会同时创建同名的非聚集索引, 以及创建同名统计信息；而键约束是靠索引来约束。


        若是对键生成的索引直接删除，则报错！

          DROP INDEX [IX_TableUniqueKey_name] ON [dbo].[TableUniqueKey]


          Msg 3723, Level 16, State 5, Line 1
          An explicit DROP INDEX is not allowed on index 'dbo.TableUniqueKey.IX_TableUniqueKey_name'. It is being used for UNIQUE KEY constraint enforcement.


          正确的删除方法，是删除表约束。

            ALTER TABLE [dbo].[TableUniqueKey] DROP CONSTRAINT [IX_TableUniqueKey_name]



            继续测试，现在对另一张表创建索引。





              CREATE UNIQUE NONCLUSTERED INDEX [IX_UniqueIndex_name] ON [dbo].[TableUniqueIndex]([name] ASC)





              创建索引, 同时创建同名统计信息。


              对索引删除后，我们再重建，对比再看看。

                DROP INDEX [IX_UniqueIndex_name] ON [dbo].[TableUniqueIndex]


                GO


                ALTER TABLE [dbo].[TableUniqueKey] ADD CONSTRAINT [IX_TableUniqueKey_name] UNIQUE NONCLUSTERED([name] ASC)


                GO


                CREATE UNIQUE NONCLUSTERED INDEX [IX_UniqueIndex_name] ON [dbo].[TableUniqueIndex]([name] ASC)


                GO


                SELECT object_name(object_id),name,index_id,type_desc,is_unique,is_unique_constraint


                FROM sys.indexes WHERE object_name(object_id) IN ('TableUniqueKey','TableUniqueIndex')










                键约束 [TableUniqueKey] 不是 check 约束，是属于一种为 UNIQUE_CONSTRAINT 的约束。而他们的索引都有性约束。


                此外，还可以通过以下检查他们的区别：

                  EXEC sp_helpconstraint 'TableUniqueKey' 


                  EXEC sp_helpconstraint 'TableUniqueIndex' 






                  EXEC sp_helpindex 'TableUniqueKey' 


                  EXEC sp_helpindex 'TableUniqueIndex' 






                  --  EXEC sp_help 'TableUniqueKey' 


                  --  EXEC sp_help 'TableUniqueIndex'












                  对比索引描述中，键 比 索引多了 unique key 。

                    --键不出错


                    EXEC sp_help [IX_TableUniqueKey_name]


                    EXEC sp_helpindex [IX_TableUniqueKey_name]


                    EXEC sp_helpconstraint [IX_TableUniqueKey_name]


                    --索引出错


                    EXEC sp_help [IX_UniqueIndex_name]


                    EXEC sp_helpindex [IX_UniqueIndex_name]


                    EXEC sp_helpconstraint [IX_UniqueIndex_name]









                    上面可以看出,不同的是: 键 比 索引 多了一种叫做 "unique key" 的约束。


                    现在禁用索引，再插入重复数据。

                      --禁用索引/约束（均可被禁用）


                      ALTER INDEX [IX_TableUniqueKey_name] ON [dbo].[TableUniqueKey] DISABLE


                      ALTER INDEX [IX_UniqueIndex_name] ON [dbo].[TableUniqueIndex] DISABLE


                      --插入重复值，正常


                      INSERT INTO [dbo].[TableUniqueKey] SELECT 3,NULL 


                      INSERT INTO [dbo].[TableUniqueIndex]SELECT 3,NULL






                      数据都能正常插入，约束或索引被禁用了。对于键约束，也是禁用索引吗？函数 ObjectProperty() 的参数 CnstIsDisabled 可以确认约束是否被禁用。

                        SELECT ObjectProperty(object_id('IX_TableUniqueKey_name'),'CnstIsDisabled')


                        结果为 0 ，即约束没有被禁用，也就是说禁用的是索引，键约束中，性是依赖于其默认创建的索引来约束的！


                        现在重建索引：


                          --删除重复


                          DELETE FROM [dbo].[TableUniqueKey] WHERE ID = 3


                          DELETE FROM [dbo].[TableUniqueIndex] WHERE ID = 3


                          /*对索引的更改*/


                          --重建索引


                          ALTER INDEX [IX_TableUniqueKey_name] ON [dbo].[TableUniqueKey] REBUILD


                          ALTER INDEX [IX_UniqueIndex_name] ON [dbo].[TableUniqueIndex] REBUILD


                          --更改部分索引参数


                          ALTER INDEX [IX_TableUniqueKey_name] ON [dbo].[TableUniqueKey] SET ( ALLOW_ROW_LOCKS = ON )


                          ALTER INDEX [IX_UniqueIndex_name] ON [dbo].[TableUniqueIndex] SET ( ALLOW_ROW_LOCKS = ON )










                          两种索引其实还是可以更改一下参数的。使用窗口打开查看，键约束的索引有些是不能更改的。

                          键约束的索引不能像正常的索引使用太多的索引参数，因为键约束与其索引同在。而单独创建的索引可以设置更多的参数,如 PAD_INDEX, FILLFACTOR, IGNORE_DUP_KEY, DROP_EXISTING, STATISTICS_NORECOMPUTE, and SORT_IN_TEMPDB 。


                          总的来说，键键约束和索引功能是一样的："性" + "索引"


                          键键约束：只是作为一种独特的约束(如主键约束,键约束,check约束,外键约束 的一种)，以约束的形式管理.但是同时又自动创建了非聚集索引，也就有了索引的性能和部分功能.实际上键约束是用索引来约束的。


                          索引：就是一种索引，它对某字段进行性检查，同时可以设置各种参数，非常灵活。


                          那么我们在创建列的性时，到底使用哪一种较好呢?

                          键约束在表中是必定存在的约束的，键约束的索引存在于一个分区中，并且不会像索引那样可以更改。因为索引可以随时改动（当然也不会经常改动），个人建议索引还是用索引更灵活。管理约束还得管理索引，而管理索引，一个就好了。但是对于一些高可用性，也要注意索引是否在其他地方也存在。


                          本文来源https://www.modb.pro/db/52933

                          相关文章