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