ALTER TABLE constraint problem

I'm trying to run the following SQL statement:

IF OBJECT_ID('MyTable') IS NOT NULL
    DROP TABLE MyTable

SELECT
    a.UserId
INTO
   开发者_StackOverflow中文版 MyTable
FROM
    UsersTable a
WHERE
    a.UserId='12359670-1DC9-4A0A-8AE5-29B664C1A57E'


ALTER TABLE MyTable ALTER COLUMN UserId UNIQUEIDENTIFIER NOT NULL
ALTER TABLE MyTable ADD PRIMARY KEY(UserId) 

However, I get the following error: Cannot define PRIMARY KEY constraint on nullable column in table 'MyTable'.

Any ideas?

This assumes SQL Server based on UNIQUEIDENTIFIER

Put a GO between (or relevant batch separator if not SQL Server)

....
ALTER TABLE MyTable ALTER COLUMN UserId UNIQUEIDENTIFIER NOT NULL
GO
ALTER TABLE MyTable ADD PRIMARY KEY(UserId)

At batch compile time, the column is nullable. So break up the batches.

SQL isn't a line by line procedural language

You'll have to do this in a stored procedure

ALTER TABLE MyTable ALTER COLUMN UserId UNIQUEIDENTIFIER NOT NULL
EXEC('ALTER TABLE MyTable ADD PRIMARY KEY(UserId)')

Found a solution. I'm using the following statement:

EXEC sp_ExecuteSQL N'ALTER TABLE MyTable ALTER COLUMN UserId UNIQUEIDENTIFIER NOT NULL'
EXEC sp_ExecuteSQL N'ALTER TABLE MyTable ADD PRIMARY KEY(UserId)'

You don't need dynamic SQL. Just create the table and the key first.

CREATE TABLE MyTable (UserId UNIQUEIDENTIFIER NOT NULL PRIMARY KEY);

INSERT INTO MyTable (UserId)
SELECT UserId
FROM UsersTable
WHERE UserId='12359670-1DC9-4A0A-8AE5-29B664C1A57E';