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

Question

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

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
    )        
  );

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

Answer 1

SQL Server's CREATE TYPE DDL statement支持至少两种不同的方式来声明 PRIMARY KEY约束和其他选项。

• 最简单的是内联 PRIMARY KEY单列上的修饰符( see the <column_definition> syntax rule ，并且 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和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 ，和CHECK约束作为表类型约束，位于列定义之后:

  • 如果您有复合 PRIMARY KEY，则必须使用此方法- 或者如果您想引用 UNIQUE 中的多个列或CHECK约束。

  • 与正常 CREATE TABLE 不同声明这些约束总是匿名并且缺少 CONSTRAINT关键字。

    • 即仅使用 PRIMARY KEY ( DistCritTypeId, ItemAction )但不是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 之后声明约束(如果有)。

  • 与CREATE TABLE不同DDL语句，不能使用CREATE INDEX定义表类型的索引； INDEX定义必须是 CREATE TYPE 的一部分声明。

  • 此外，与 PRIMARY KEY 不同, CHECK ，和UNIQUE到目前为止我们看到的约束，INDEX表类型上的对象不是匿名的。

    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这里的约束类型:表类型不能参与外键约束。