How to add an index or primary key to a user-defined table type in SQL Server?

@bernd_K and @gbn's answers work if it's a single column PK. For multi column, it would be:

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

In short, you can have PKs and UNIQUE table constraints, but you cannot name them. This kind of makes sense, since you're going to be creating multiple objects of the same type, and the only time you're going to want to work with these constraints would be an alteration of the entire table type.

You also cannot define indexes, since those are primarily an artifact around physical storage.

SQL Server's CREATE TYPE DDL statement supports at least two different ways of declaring the PRIMARY KEY constraint and other options.

• The simplest is an inline PRIMARY KEY modifier on a single column (see the <column_definition> syntax rule, and the CLUSTERED/NONCLUSTERED keyword is optional).

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

  • Note that UNIQUE and CHECK constraints can also be declared on a single column. For example:

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

• You can also declare the PRIMARY KEY, UNIQUE, and CHECK constraints as a table-type constraint, located right after the column definitions:

  • You must use this approach if you have a composite PRIMARY KEY - or if you want to reference multiple columns in your UNIQUE or CHECK constraint.

  • Unlike in a normal CREATE TABLE statement these constraints are always anonymous and lack the CONSTRAINT keyword.

    • i.e. use just 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 )
    );
    

• You can also specify additional arbitrary INDEX objects, which are declared after the PRIMARY KEY constraint (if any).

  • Unlike CREATE TABLE DDL statements, you cannot use CREATE INDEX to define an index on a table-type; the INDEX definition must be part of the CREATE TYPE statement.

  • Also, unlike the PRIMARY KEY, CHECK, and UNIQUE constraints we've seen so far, INDEX objects on table-types are not anonymous.

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

• Note there is no FOREIGN KEY constraint type here: table-types cannot participate in foreign-key constraints.