Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 501
  • Last Modified:

Table variable and constraints

Running SQL server 2000  on Windows 2000 server

I have created a table variable.  I cannot seem to assign a primary key.
I get the error:
  Incorrect syntax near the keyword 'CONSTRAINT'.

See my code below:

What am I doing wrong?
DECLARE @tblAmibrokerRawTemp	table
    (Symbol		varchar(7)	NOT NULL	,
     EntryDate		smalldatetime	NOT NULL	
     	CONSTRAINT tblAmibrokerRawTemp_PK PRIMARY KEY(Symbol, EntryDate),
     Active		smallint	NOT NULL	DEFAULT 1)

Open in new window

0
donpick
Asked:
donpick
2 Solutions
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
This should work:
DECLARE @tblAmibrokerRawTemp    table
    (Symbol             varchar(7)      NOT NULL        ,
     EntryDate          smalldatetime   NOT NULL        ,
     Active             smallint        NOT NULL        DEFAULT 1,
     CONSTRAINT tblAmibrokerRawTemp_PK PRIMARY KEY(Symbol, EntryDate)
)

Open in new window

0
 
Aneesh RetnakaranDatabase AdministratorCommented:
i dont think you can do that with a table variable, you need to convert that into a temp table
0
 
chapmandewCommented:
you can assign pk's on table variables
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
appariCommented:
try this,
for table type variables cannot specify constraint name so "Constraint constraintname" part is not required

DECLARE @tblAmibrokerRawTemp    table
    (Symbol             varchar(7)      NOT NULL        ,
     EntryDate          smalldatetime   NOT NULL        ,
     Active             smallint        NOT NULL        DEFAULT 1,
     PRIMARY KEY(Symbol, EntryDate)
)
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
appari was correct,
I don't have access to server to check this when I posted..

DECLARE @tblAmibrokerRawTemp    table
    (Symbol             varchar(7)      NOT NULL        ,
     EntryDate          smalldatetime   NOT NULL        ,
     Active             smallint        NOT NULL        DEFAULT 1,
     PRIMARY KEY(Symbol, EntryDate)
)
0
 
donpickAuthor Commented:
This is not documented anywhere I can find.  Thank you for your help.
0
 
appariCommented:
it is documented,
see syntax part of table datatype from here
http://msdn.microsoft.com/en-us/library/ms175010.aspx
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now