Help with alter table statement

Question

Post reply

Help with alter table statement

stricknyn

SSCertifiable

Points: 6560
More actions
January 6, 2010 at 3:48 pm

#215373

Hello all,
Can someone tell me what I'm doing wrong here?
ALTER TABLE #PAYEES
(PAYEE_ID INT NOT NULL)
ADD CONSTRAINT PK_payeeID PRIMARY KEY CLUSTERED (PAYEE_ID) ON [PRIMARY]
PAYEE ID ASC
The error I get is:
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near '('.
Thanks,
Strick

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

it looks like you have not added the PAYEE_ID yet:

ALTER TABLE #PAYEES

--add first item: column

ADD PAYEE_ID INT NOT NULL,

--add second item: constraint

CONSTRAINT PK_payeeID PRIMARY KEY CLUSTERED (PAYEE_ID)

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Greg Charles SSC-Forever Points: 45403 More actions · Answer 2

The column definition is causing the error. Assuming the column is already in the table, remove (PAYEE_ID INT NOT NULL) and you won't get the syntax error. Also remove PAYEE ID ASC because ascending and descending isn't specified for a primary key constraint.

Greg

stricknyn SSCertifiable Points: 6560 More actions · Answer 3

Hi Guys,

Thanks for your help. Actually the table already had the payee_id. I just needed to put the key on it. I also had to set the column to not null since I got an error stating I couldn't add a primary key on a nullable column.

After some fiddling with it a bit I was able to get it working:

ALTER TABLE #PAYEES

ALTER COLUMN PAYEE_ID INT NOT NULL

ALTER TABLE #PAYEES

ADD CONSTRAINT PK_payeeID PRIMARY KEY CLUSTERED (PAYEE_ID)

One question I had though is, is there a way I can do both in one alter table statement? When I tried to do it in one statement:

ALTER TABLE #PAYEES

ALTER COLUMN PAYEE_ID INT NOT NULL

ADD CONSTRAINT PK_payeeID PRIMARY KEY CLUSTERED (PAYEE_ID)

I got the error:

Msg 156, Level 15, State 1, Line 5

Incorrect syntax near the keyword 'CONSTRAINT'.

Another quick question, how did you guys enclose your T-SQL to make it better readable in these posts?

Thanks!

Strick