How To add composite unique key to user defined table Type

Question

How To add composite unique key to user defined table type like this :

CREATE TYPE [dbo].[jobdata] AS TABLE(
    [emp_num] [smallint] NULL,
    [job_date] [date] NULL,
    [year] [smallint] NULL,
    [job_code] [smallint] NULL,
    [order_year] [smallint] NULL,
    [order_ser] [decimal](5, 0) NULL,

)
GO

I want emp_num,job_date to be composite unique key .

Answer 1

You Can't Alter UserDefined table types ,You need to drop and recreate again for any changes..

From MSDN..

User-defined types cannot be modified after they are created, because changes could invalidate data in the tables or indexes. To modify a type, you must either drop the type and then re-create it, or issue an ALTER ASSEMBLY statement by using the WITH UNCHECKED DATA clause.

Below is the way to create unique constraint on UserDefined Table Type

CREATE TYPE test AS TABLE 
( col1 VARCHAR(50)
, col2 INT ,
 unique (col1,col2)
);

Note:We can't name Constraints,so creating constraints like normal way is not valid..

Example below

 CREATE TYPE test AS TABLE 
    ( col1 VARCHAR(50)
    , col2 INT ,
    constraint test  unique (col1,col2)
    );