Enforce uniqueness on null

Questions / Help
, ,
1

I have a set of Events, that have a date. The dates are only given in full days (ie 2022-08-06). To keep track of the order of Events within one day, I keep a previous_event_id key for each event so that I can create a linkedlist. When I create a first Event on a particular day, it will have a previous_event_id of NULL.

So a set of events in my DB could look something like this:

id date previous_event_id
1 2022-01-01 NULL
2 2022-01-01 1
3 2022-01-01 2
4 2022-01-02 NULL
5 2022-01-02 4
6 2022-01-02 5
7 2022-01-03 NULL

Now, I want to enforce that only one event per day can have a NULL value. Is this even possible, and if so, how do I do this? I have the following code in my model but the unique constraint allows multiple NULL values.


def changeset(event, attrs) do
    event
    |> cast(attrs, [
      :name,
      :date,
      :previous_event_id
    ])
    |> unique_constraint([:previous_event_id, :date])
    |> validate_required([
      :name,
      :date,
    ])
  end

I considered using -1 instead of NULL, but (when not NULL / -1) it is also a reference, so then I would have a validation error on -1 not being an existing event id.

So, how (if at all) do I enforce a table to have only one NULL row? (Or, in this case, only one NULL row per date).

1 Like
2

You can do this by making a “partial index” that has a where clause:

create index(:events, [:date], where: "previous_event_id is null", unique: true)
3 Likes
3

Friendly reminder (for future readers that may find this), if you want only one NULL per table, PostgreSQL 15 (which is at Beta 2 at the moment) has support for treating null values as not distinct and you can enforce this with a simple unique constraint instead of a partial index.

https://www.postgresql.org/docs/15/ddl-constraints.html#DDL-CONSTRAINTS-UNIQUE-CONSTRAINTS

image

3 Likes