Composite Primary Keys (or multiple unique keys) on Ecto Schema

Questions / Help
, ,
1

I’m wondering if I’ve wandered into trouble with my Ecto schemas. I have one that describes files in S3. For convenience (e.g. for REST endpoints), it’s nice to have a singular primary key. But it’s also sane to have a unique constraint on the combination of bucket + object_key. There are a couple ways of describing this for PostGres in the migration. My question is how to describe this in the Ecto schema?

One way is this:

  @primary_key {:id, :binary_id, autogenerate: true}
  schema "files" do
    field(:bucket, :string, primary_key: true)
    field(:object_key, :string, primary_key: true)
    # etc.
    timestamps()
  end

Is that correct? Or is it incorrect because it infers that the composite key is actually id + bucket + object_key?

or would this be more correct:

  @primary_key {:id, :binary_id, autogenerate: true}
  schema "files" do
    field(:bucket, :string)
    field(:object_key, :string)
    # etc.
    timestamps()
  end

How to describe the critical importance of the bucket + object_key constraint?

Thanks in advance for clarity on this!

2

If you have a synethetic :id column already, then there is nothing at the schema level that you need to do in Ecto. The database will enforce it for you as long as you set the unique index on the column pair.

It may be useful to add a unique_constraint call to any changesets you build with this schema though to provide a nice error message should the constraint be violated.

1 Like
3

Thanks – that makes sense.

What I’m really after is doing some “reflection” on the schema modules in an abstraction layer – in those cases it’s helpful to be able to inspect the schema definition at runtime, e.g. to know which fields could uniquely identify an existing database row. I could define a behaviour and have my schemas implement it, e.g. to disclose the composite keys in use.

I wonder if the field/3 function could be augmented to support the storage of some metadata that could be available to inspection via reflection, or if this is too specific and strange a use-case.

4

That, or simply having a lookup table e.g. Identifiable.get_primary_key(module_expected_here) are likely the least painful options.