Should we model zero-to-many relations in Ecto as reference or regular field?

Questions / Help
1

I have a staging table that takes unvalidated, external data and uploads it into an archived “upload” table before “posting” the data officially into into the core system. After posting, I’d like to make a reference from the core back to that uploaded, raw data.

I was trying to accomplish this via a zero-to-many relation between staged_transactions and transactions where staged_transactions.txn_id == transactions.id. I was thinking I could just specify null: true in the Ecto.migrations.add.references() area but for some reason, this generates a constraint in the database that enforces NOT NULL values.

I suppose I could just do away with the Ecto.references approach and just add the field as a “regular” field w/o “references” but wanted to check in case there’s something I’m not understanding fully. Is is possible to make code below work?

     # Will be NULL when transaction hasn't been posted first time yet.
      add :txn_id,
          references(
            :transactions,
            column: :id,
            type: :binary_id,
            on_delete: :restrict,
            on_update: :update_all
          ),
          null: true
2

This should work just fine with PostgreSQL - I did add :some_column references(...), null: true in the past and it worked. The thing that Ecto is not great at is migrations that change the NOT NULL constraint though.