Sorry, Lowell, but you misread the code. Gail and you are wrong in this case, and hlrx is right. The first batch creates a table with a column that is not nullable. The second batch changes it to allow nulls and insert a null. When that batch compiles, the column is not nullable, and yet the code compiles. The same behaviour is not used for ALTER TABLE ADD PRIMARY KEY.

Works:

CREATE TABLE dbo.test1 (col1 INT NOT NULL);

GO

ALTER TABLE dbo.test1 ALTER COLUMN col1 INT NULL;

INSERT dbo.test1 VALUES (NULL);

GO

DROP TABLE dbo.test1;

GO

Fails:

CREATE TABLE dbo.test1 (col1 int NULL);

GO

ALTER TABLE dbo.test1 ALTER COLUMN col1 int NOT NULL;

ALTER TABLE dbo.test1 ADD PRIMARY KEY (col1);

GO

DROP TABLE dbo.test1;

GO

This is definitely a difference between how ALTER TABLE is handled vs how INSERT is handled (and not a difference between checks for NULL vs checks for NOT NULL). This can be seen from the following code:

-- Run time error

CREATE TABLE dbo.test1 (col1 int NULL);

GO

ALTER TABLE dbo.test1 ALTER COLUMN col1 int NOT NULL;

PRINT 'Executing';

INSERT dbo.test1 VALUES (NULL);

GO

DROP TABLE dbo.test1;

GO

-- Compile time error

CREATE TABLE test1 (col1 int NULL);

GO

ALTER TABLE test1 ALTER COLUMN col1 int NOT NULL;

ALTER TABLE test1 ADD PRIMARY KEY (col1);

GO

DROP TABLE dbo.test1;

GO

-- No error

CREATE TABLE dbo.test1 (col1 int NOT NULL);

GO

ALTER TABLE dbo.test1 ALTER COLUMN col1 int NULL;

PRINT 'Executing';

INSERT dbo.test1 VALUES (NULL);

GO

DROP TABLE dbo.test1;

GO

Now while all this is very interesting (in my own special geeky way), it is not very relevant for the original question. Like it or not, consistent or not, it is how SQL Server works and we cannot change it. If hlrx wants the code to run, (s)he will have to use a separate batch for the ALTER TABLE ADD PRIMARY KEY.