1

I know sql doesn't allow null values in pk.

but assume we have some nulls in pk, what we can do about it in practice?

my thoughts

  1. check other tables to see if we can recover nulls in pk
  2. ignore null values, as they are not needed to be unique

please correct me if different thoughts and what else we can do?

let's generalise the question a bit:

forget about pk, imagine we have a unique column with some nulls, what do you do about nulls? (we can treat this column as pk even if it is not declared as pk)

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
Momoco
  • 41
  • 5
  • 1
    When Null value detected in primary key expression server locks the table as damaged and forbids access to it until successful repair. – Akina Jan 02 '21 at 16:57
  • 1
    _but assume we have some nulls in pk_ it is not possible to assume something imposible – nacho Jan 02 '21 at 17:26
  • 1
    @Momoco you cannot assume such thing as mysql simply does not allow null values in a PK. End of story. – Shadow Jan 02 '21 at 17:28

2 Answers2

4

If you have NULL values, you do not have a primary key. That is simply the definition of relational databases.

If you want the value to be unique, then declare the column as unique. Use another column (or columns) as the primary key.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
2

assume we have some nulls in pk, what we can do about it in practice?

You have two choices:

  • UPDATE the rows and set non-NULL values in that column before you declare it as a primary key.
  • DELETE the rows that have NULL in that column before you declare it as a primary key.

You cannot declare a primary key constraint on a column if it has NULLs. This causes an error.

Likewise, you can't insert a NULL in the primary key column afterwards. This causes an error too, because if it's a primary key, the column must have the NOT NULL option.

Let's generalise the question a bit: forget about pk, imagine we have a unique column with some nulls, what do you do about nulls?

If you're talking about a more general case of a column with a unique key, it is okay to declare a unique key on a nullable column.

Depending on the purpose of the column, it could be legitimate to use NULL where the row has no value. But it must be an attribute column, not the primary key.

(we can treat this column as pk even if it is not declared as pk)

No, sorry, you cannot even treat this column as a de facto primary key if it allows NULLs. The purpose of a primary key is not only to be unique, but to provide a way for a query to select each row individually by value. Since a nullable unique key can have any number of rows with NULL (notwithstanding the nonstandard behavior of Microsoft SQL Server), a NULL is not able to unambiguously select each row individually.

It's hard to understand why you are asking this question. Can you describe the scenario more concretely? What is the problem you're trying to solve? I am suspecting that this is an XY Problem.

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828