Composite primary key on a table variable?

[Shaun Evans]

Is is possible to create a composite primary key on a table variable?
Neither of these two statements are successful:

DECLARE @opmcjf TABLE (
jobdetailid INT NOT NULL,
cjfid INT NOT NULL,
cjfvalue VARCHAR(100) NULL
)

ALTER TABLE @opmcjf ADD CONSTRAINT [PK_opmcjf] PRIMARY KEY CLUSTERED
(
[jobdetailid],
[cjfid]
)

and

DECLARE @opmcjf TABLE (
jobdetailid INT PRIMARY KEY,
cjfid INT PRIMARY KEY,
cjfvalue VARCHAR(100) NULL
)

Thanks,
Shaun

[Simon Hayes]

"Shaun Evans" <se********@hotmail.com> wrote in message
news:37**************************@posting.google.c om...

Is is possible to create a composite primary key on a table variable?
Neither of these two statements are successful:

DECLARE @opmcjf TABLE (
jobdetailid INT NOT NULL,
cjfid INT NOT NULL,
cjfvalue VARCHAR(100) NULL
)

ALTER TABLE @opmcjf ADD CONSTRAINT [PK_opmcjf] PRIMARY KEY CLUSTERED
(
[jobdetailid],
[cjfid]
)

and

DECLARE @opmcjf TABLE (
jobdetailid INT PRIMARY KEY,
cjfid INT PRIMARY KEY,
cjfvalue VARCHAR(100) NULL
)

Thanks,
Shaun

This should work - see "table" in Books Online.

DECLARE @opmcjf TABLE (
jobdetailid INT NOT NULL,
cjfid INT NOT NULL,
cjfvalue VARCHAR(100) NULL,
primary key ([jobdetailid],[cjfid])
)

Simon

[David Portas]

This is the syntax:

DECLARE @opmcjf TABLE (jobdetailid INTEGER, cjfid INTEGER, cjfvalue
VARCHAR(100) NULL, PRIMARY KEY (jobdetailid,cjfid))

AFAIK it isn't possible to alter a table-variable after it's declared. Since
a table-variable is scoped to a batch I guess that functionality wouldn't be
very useful.

--
David Portas
SQL Server MVP
--