- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
06-11-2025 05:39 AM
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
06-11-2025 08:19 AM
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 ).
