Cannot define PRIMARY KEY constraint on nullable column in table 'tp_ladder'

I'm trying to run the following SQL statement

--dropping customer TABLE--
DROP TABLE tp_ladder;

--creating ladder TABLE
CREATE TABLE tp_ladder (
    ladder_id         INTEGER,
    ladder_type       VARCHAR(50),
    ladder_name       VARCHAR(50) NOT NULL,
    ladder_discount   DECIMAL(3,2),
    ladder_price      DECIMAL(8,2) NOT NULL,
    ladder_weight     DECIMAL(5,2),
    ladder_height     DECIMAL(5,2),
    ladder_rating     DECIMAL(10,2),
    warehouse_id      INTEGER    
);

--creating primary key for ladder table
ALTER TABLE tp_ladder

ADD CONSTRAINT tp_ladder_ladder_id
PRIMARY KEY(ladder_id);

However I receive the error message:

Cannot define PRIMARY KEY constraint on nullable column in table 'tp_ladder'

Any advice?

Solution

The error is quite clear, but why it is an error is not obvious.

Other databases (such as MySQL and Postgres), do allow you to do what you want -- adding a primary key on a column that is not explicitly declared as NOT NULL. After all, PRIMARY KEY imposes a NOT NULL constraint as well. This is surprising, especially on an empty table. So, the error is not obvious.

Further, SQL Server stores the null flags for all columns, even those that are declared NOT NULL. So, even if there were data, then the data would not need to change (assuming there are no NULL values). Not all databases store NULL flags the same way.

If you have defined the table, you can modify the column using:

ALTER TABLE tp_ladder ALTER COLUMN ladder_id INT NOT NULL

This will allow you then add the primary key constraint.

But, I recommend doing it in-line when you create the table:

ladder_id INT PRIMARY KEY

Note that when defined in the CREATE TABLE, the NOT NULL is not needed (it is actually redundant).