Composite Key on Table Data Type

[SallyCinnamon]

I am trying to add indexes to my table data types and have realized that I can only add primary keys. So, I am hoping there is a way to add a composite primary key, but I am not having any success. I have tried the following:

Declare @Sales TABLE
(SaleID INT IDENTITY(100000,1),
SalesRegion CHAR(2),
CONSTRAINT ID_PK PRIMARY KEY (SaleID,SalesRegion))

Any suggestions would be appreciated.

Thanks!

[rmiao]

Indexes or other constraints applied to the table data type must be defined as part of the DECLARE variable or CREATE FUNCTION statement. They cannot be applied later, because the CREATE INDEX or ALTER TABLE statements cannot reference table variables and user-defined functions. Check 'DECLARE @local_variable' in books online.

[SallyCinnamon]

Rmiao,

I realize this is true. That is why in the above example I added the constraint as part of the declare statement. Are you saying that you think it can not be done? Or do you have an example of how to do it. I have reviewed the SQL documentation for Create Table and Declare many times before posting this.

Thanks!

[WizKid]

Declare @Sales TABLE
(SaleID INT IDENTITY(100000,1),
SalesRegion CHAR(2),
PRIMARY KEY (SaleID,SalesRegion))

[SallyCinnamon]

WizKid,

This is exactly what I wanted. I just coudn't get the syntax right. Thanks so much!