Create a combined primary key with a one column accepting NULL

I have a table CustomerLocation with the following columns :

• Country
• Region
• City

I want to create a composite primary key with the 3 columns mentioned above knowing that the Region column is NULLABLE.

Answer

You can’t. The definition of primary key specifies that only non-NULL values are allowed. This is clearly explained in the documentation:

All columns defined within a PRIMARY KEY constraint must be defined as NOT NULL. If nullability is not specified, all columns participating in a PRIMARY KEY constraint have their nullability set to NOT NULL.

That said, the documentation doesn’t explain that this is consistent with the SQL standard and the definition of primary keys and NULL in all databases.

But I would recommend that you have a single integer key for the table:

create table CustomerLocation as (
    CustomerLocationId int identity(1,1) primary key,
    . . .
);

You can define a unique constraint on the three columns, but foreign key references should use the primary key.