Search code examples
sqlprimary-keyalter

SQL - Running multiple ALTER queries

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!

Solution

  • 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