topic Unable to add primary key constraint to nullable identity column in Data Engineering

Unable to add primary key constraint to nullable identity column

Malthe — Wed, 11 Jun 2025 12:39:27 GMT

While we can in fact define a primary key during table creation for an identity column that's nullable (i.e., not constrained using NOT NULL), it's not possible to add such a primary key constraint after the table has been created.

We get an error message:
> Cannot create the primary key `my_table_pk` because its child column(s) `id` is nullable. Please change the column nullability and retry.

But it's not possible to change the nullability of an identity column:
> ALTER TABLE ALTER COLUMN is not supported for IDENTITY columns.

I'm not sure if logically speaking this is a bug, but it's a bit of an unfortunate situation if you want to move to a table setup with constraints and haven't created the identity columns initially with a nullability constraint.

Re: Unable to add primary key constraint to nullable identity column

amuchoudhary — Wed, 11 Jun 2025 15:19:12 GMT

Creating a table with a nullable IDENTITY column and defining the primary key at creation time works.
The database quietly interprets the column as NOT NULL for the purposes of the primary key, even though it's technically defined as nullable (i.e., not constrained using NOT NULL).

The database engine enforces the nullability at the schema level due to which this contradictory error shows up.
As of today there is no direct fix, maybe we can add this as enhancement but not sure its going to fix anytime soon as this will get tricky and dirty.
The best approach is dropping the table and recreating it with required constraints during creation ( if possible ).