Can I use a nullable column in the primary key?

[cian-bayer]

We have a composite key on a table:

  @override
  Set<Column> get primaryKey =>
      {column1, column2, column3};

In this case, column1 and column2 are required, but column3 is optional. When column3 is null, duplicate rows can be inserted into the database. Is this normal behavior?

Ideas on a work-around? There is an additional column we have that might serve as a better uniqueness indicator, but I wanted to know if there was a better built-in way to having Drift treat two nulls as the same value.

[cian-bayer]

FWIW, I've found some discussion that nullable columns in a primary key is not always standard database design, and it seems like some databases will not treat two nulls as identical: https://stackoverflow.com/questions/386040/whats-wrong-with-nullable-columns-in-composite-primary-keys

[simolus3]

There's not much drift could do here since it just uses sqlite3 under the hood, which documents this as follows:

Each row in a table with a primary key must have a unique combination of values in its primary key columns. For the purposes of determining the uniqueness of primary key values, NULL values are considered distinct from all other values, including other NULLs. [...]
According to the SQL standard, PRIMARY KEY should always imply NOT NULL. Unfortunately, due to a bug in some early versions, this is not the case in SQLite. [...] Hence, it has been decided to merely document the fact that SQLite allows NULLs in most PRIMARY KEY columns.

So it seems like yes, this is the expected (although not ideal) behavior and column3 probably shouldn't be included as a primary key.
Since primary and unique keys also can't be created over arbitrary expressions, I can't really think of a workaround here. Can you express column3 as a non-nullable column by using a default value to indicate that no value has been set? Then you can include it in the primary key without these issues.

[cian-bayer]

Gotcha, so, column3 really shouldn't be non-nullable - we were going back and forth between having a default -1 value or something, but I think keeping it null is important here.

Let's say I changed the primary key to be a different column that might be a better indicated of uniqueness

  @override
  Set<Column> get primaryKey =>
      {column4};

I tried this, but I got conflicts because there were duplicate rows in the database that conflicted after this constraint. (If the column3 null issue wasn't present and duplicate data wasn't downloaded, there wouldn't be conflicts.)

How would I resolve primaryKey constraint failures during a migration?? In this case I wouldn't care which of the duplicates were kept.

And sorry if this is better documented elsewhere, I've given the migration docs a look and didn't see anything about this.

Thanks

[simolus3]

we were going back and forth between having a default -1 value or something, but I think keeping it null is important here.

It's a bit counter-intuitive, but you could apply a type converter mapping from nullable values in Dart to a non-null value in SQL. But then it's important to remember that SQL features like IS NULL will no longer work.

How would I resolve primaryKey constraint failures during a migration?? In this case I wouldn't care which of the duplicates were kept.

The best way to drop duplicates (that I can think of) is to use a custom query with common table expressions. I think this query would drop duplicates across column1, column2 and column4, for instance:

WITH
  first_row(id, c1, c2, c4) AS (SELECT min(rowid), column1, column2, column4 FROM tbl GROUP BY column1, column2, column4)
DELETE FROM tbl WHERE rowid != (SELECT f.id FROM first_row f WHERE f.c1 = tbl.column1 AND f.c2 = tbl.column2 AND f.c3 = tbl.column4)

You can't use WITH in the Dart query builder yet, but you can use customStatement to run this query before migrating to the new primary key.

[cian-bayer]

gotcha, I might have to bug you one more time, but this is helpful. Thanks a lot