SQL - Running multiple ALTER queries

Question

i'm having trouble with running multiple ALTER commands in a single query.

Whenever im trying to run the following code :

ALTER TABLE Book$
ALTER COLUMN PID INT NOT NULL

ALTER TABLE Book$
ADD CONSTRAINT pk_book PRIMARY KEY(PID)

I get an error :

Cannot define PRIMARY KEY constraint on nullable column in table 'Book$'.

But if i run the queries separately , first :

ALTER TABLE Book$
ALTER COLUMN PID INT NOT NULL

And then :

ALTER TABLE Book$
ADD CONSTRAINT pk_book PRIMARY KEY(PID)

Everything seems to work just fine. What am i doing wrong? Thanks!

Answer 1

Add GO(batch separator) in between to fix the problem

ALTER TABLE Book$
ALTER COLUMN PID INT NOT NULL

GO

ALTER TABLE Book$
ADD CONSTRAINT pk_book PRIMARY KEY(PID)

Without GO the entire script will be considered as single script

Answer 2

I'm not sure which database you are using. To explain what is happening, though, you need to understand two phases of statement processing: Compilation and Execution.

The Compilation phase reads the statement and defines the execution plan. The Execution phase then runs the plan. Nothing about the table changes just because a statement is compiled.

What is happening is that the two statements are compiled and then executed. When the second is compiled, nothing has changed (well, except for the fact that the first statement's execution plan is stored somewhere). Hence, you are getting a compilation error.

When you run the two separately, the changes from the first take place and then the second does not generate an error.