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:

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).