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,
You Can't Alter UserDefined table types ,You need to drop and recreate again for any changes..
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..
CREATE TYPE test AS TABLE ( col1 VARCHAR(50) , col2 INT , constraint test unique (col1,col2) );