如何在 SQL Server 中向用户定义的表类型添加索引或主键？

我有一个用户定义的类型，我想向其中添加主键或索引：

IF NOT EXISTS (
  SELECT * 
  FROM sys.types st 
  JOIN sys.schemas ss 
    ON st.schema_id = ss.schema_id 
  WHERE st.name = N'DistCritGroupData' 
    AND ss.name = N'dbo')
BEGIN

  CREATE TYPE [dbo].[DistCritGroupData] AS TABLE
  (
    [DistCritTypeId] [int] NOT NULL,
    [ItemAction] [int] NOT NULL,        
    [ObjectId] [int] NOT NULL,
    [OperatorType] [int] NOT NULL     
  );

END;
GO  

我基本上想添加主键或聚集索引。我尝试了此操作，但收到错误“找不到对象“dbo.DistCritGroupData”，因为它不存在或您没有权限”。

  CREATE TYPE [dbo].[DistCritGroupData] AS TABLE
  (
    [DistCritTypeId] [int] NOT NULL,
    [ItemAction] [int] NOT NULL,        
    [ObjectId] [int] NOT NULL,
    [OperatorType] [int] NOT NULL,
    CONSTRAINT [DistCritGroupData0] PRIMARY KEY CLUSTERED 
    (
       [DistCritTypeId] ASC
    )        
  );

我在用户定义的表类型的对象资源管理器中看到有“列”、“键”、“约束”和“索引”部分。问题是，如何添加键或索引？

SQL服务器CREATE TYPEDDL语句 https://learn.microsoft.com/en-us/sql/t-sql/statements/create-type-transact-sql支持至少两种不同的声明方式PRIMARY KEY约束和其他选项。

• 最简单的是内联PRIMARY KEY单列上的修饰符 (see the <column_definition>语法规则 https://learn.microsoft.com/en-us/sql/t-sql/statements/create-type-transact-sql#syntax，以及CLUSTERED/NONCLUSTERED关键字是可选的）。

  CREATE TYPE dbo.DistCritGroupData AS TABLE (
  
      DistCritTypeId int NOT NULL PRIMARY KEY CLUSTERED,
      ItemAction     int NOT NULL,        
      ObjectId       int NOT NULL,
      OperatorType   int NOT NULL
  );
  

  • 注意UNIQUE and CHECK约束也可以在单个列上声明。例如：

    CREATE TYPE dbo.DistCritGroupData AS TABLE (
    
        DistCritTypeId int NOT NULL PRIMARY KEY CLUSTERED,
        ItemAction     int NOT NULL,        
        ObjectId       int NOT NULL UNIQUE,
        OperatorType   int NOT NULL CHECK ( OperatorType >= 1 AND OperatorType <= 10 )
    );
    

• 您还可以声明PRIMARY KEY, UNIQUE, and CHECK约束条件作为表类型约束，位于列定义之后：

  • 如果您有复合材料，则必须使用此方法PRIMARY KEY- 或者如果您想在您的UNIQUE or CHECK约束。

  • 与正常情况不同CREATE TABLE声明这些约束总是匿名的并且缺乏CONSTRAINT关键词。

    • 即仅使用PRIMARY KEY ( DistCritTypeId, ItemAction ) but not CONSTRAINT PK_DistCritGroupData PRIMARY KEY ( DistCritTypeId, ItemAction ).

    CREATE TYPE dbo.DistCritGroupData AS TABLE (
    
        DistCritTypeId int NOT NULL,
        ItemAction     int NOT NULL,        
        ObjectId       int NOT NULL,
        OperatorType   int NOT NULL,
    
        PRIMARY KEY ( DistCritTypeId ),
        UNIQUE ( ObjectId ),
        CHECK ( OperatorType >= 1 AND OperatorType <= 10 )
    );
    

• 您还可以指定额外的任意INDEX对象，这些对象在之后声明PRIMARY KEY约束（如果有）。

  • Unlike CREATE TABLEDDL语句，不能使用CREATE INDEX定义表类型的索引；这INDEX定义must成为其中的一部分CREATE TYPE陈述。

  • 另外，与PRIMARY KEY, CHECK, and UNIQUE到目前为止我们所看到的限制，INDEX表类型上的对象are not匿名的。

    CREATE TYPE dbo.DistCritGroupData AS TABLE (
    
        DistCritTypeId int NOT NULL,
        ItemAction     int NOT NULL,        
        ObjectId       int NOT NULL,
        OperatorType   int NOT NULL,
    
        PRIMARY KEY ( DistCritTypeId ),
    
        INDEX IX_ObjectId_OperatorType ( ObjectId, OperatorType )
    );
    

• 注意没有FOREIGN KEY这里的约束类型：表类型不能参与外键约束。