?
Solved

Table variable and constraints

Posted on 2010-01-12
7
Medium Priority
?
488 Views
Last Modified: 2012-05-08
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
Comment
Question by:donpick
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 26300201
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
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 26300296
i dont think you can do that with a table variable, you need to convert that into a temp table
0
 
LVL 60

Expert Comment

by:chapmandew
ID: 26300619
you can assign pk's on table variables
0
Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

 
LVL 39

Accepted Solution

by:
appari earned 1000 total points
ID: 26300694
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
 
LVL 57

Assisted Solution

by:Raja Jegan R
Raja Jegan R earned 1000 total points
ID: 26301168
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
 

Author Closing Comment

by:donpick
ID: 31676487
This is not documented anywhere I can find.  Thank you for your help.
0
 
LVL 39

Expert Comment

by:appari
ID: 26310999
it is documented,
see syntax part of table datatype from here
http://msdn.microsoft.com/en-us/library/ms175010.aspx
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction This article will provide a solution for an error that might occur installing a new SQL 2005 64-bit cluster. This article will assume that you are fully prepared to complete the installation and describes the error as it occurred durin…
In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question