Composite unique constraints

[sjtg...@mac.com]

AFAICT Ecto does not support composite constraints. Example: I have an Account model where I wish to apply a uniqueness constraint to bank_code, account_number, and enrolment_id, such that %{enrolment_id: 1, bank_code: "123", account_number: "456"} and %{enrolment_id: 1, bank_code: "999", account_number: "456"} could co-exist. The Ecto docs explain how "complex constraint", e.g. scoping to a foreign key, but constraints for n columns where n > 2 are not explained and don't seem possible (Ecto.Changeset.add_constraint/5 has a guard that changeset should be a binary).

Has anyone else encountered this? What was your solution?

Thanks

S

[Theron Boerner]

You should be able to add a unique index using unique_index (which is just a shortcut for `index(_, _, [unique: true])`)

I think you're looking for:

unique_index(:accounts, [:enrolment_id, :bank_code, :account_number], name: :something)

And then just add the unique_constraint to your Account changeset.

See:

http://hexdocs.pm/ecto/Ecto.Changeset.html#unique_constraint/3

http://hexdocs.pm/ecto/Ecto.Migration.html#unique_index/3

http://hexdocs.pm/ecto/Ecto.Migration.html#index/3

This is what the Postgres docs say about multicolumn indexes:

 

Currently, only the B-tree and GiST index types support multicolumn indexes. Up to 32 columns can be specified. 

 

http://www.postgresql.org/docs/8.3/static/indexes-multicolumn.html 

--
You received this message because you are subscribed to the Google Groups "elixir-ecto" group.
To unsubscribe from this group and stop receiving emails from it, send an email to elixir-ecto...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/elixir-ecto/856cbc95-b248-4e13-847f-e91d447edc9c%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[sjtg...@mac.com]

I can actually get this to work just by specifying one field in the model. If I specify the constraints separately in the model, e.g unique_constraint(:bank_code, name: :accounts_enrollment_id_bank_code_account_number_index) and unique_constraint(:account_number, name: :accounts_enrollment_id_bank_code_account_number_index) only one attribute has an error message (I guess Ecto fails fast in this code path?)

[sjtg...@mac.com]

Exactly, I have  create unique_index(:accounts, [:enrollment_id, :bank_code, :account_number]) in my migration, just the model part is a bit confusing.

[José Valim]

Exactly, I have  create unique_index(:accounts, [:enrollment_id, :bank_code, :account_number]) in my migration, just the model part is a bit confusing.

Yes, you need to choose *one* attribute to show the error. We could potentially fix this in Ecto but wouldn't make it more sense to show the error message just once and refer the other field in the error message? Something like "This account number was already added for the chosen bank code"?

[Steve Graham]

Yea this is what I have done. Thanks for your guidance on this.

Sent from my iPhone

On 7 Dec 2015, at 08:57, José Valim <jose....@plataformatec.com.br> wrote:

Exactly, I have  create unique_index(:accounts, [:enrollment_id, :bank_code, :account_number]) in my migration, just the model part is a bit confusing.

Yes, you need to choose *one* attribute to show the error. We could potentially fix this in Ecto but wouldn't make it more sense to show the error message just once and refer the other field in the error message? Something like "This account number was already added for the chosen bank code"?

--
You received this message because you are subscribed to a topic in the Google Groups "elixir-ecto" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/elixir-ecto/hN0IleIo-d8/unsubscribe.
To unsubscribe from this group and all its topics, send an email to elixir-ecto...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/elixir-ecto/CAGnRm4J_ij9qgt8Ga%3DR6DqR8EHh4E0Goeid%3DrRG9c%3DXfRhXJJQ%40mail.gmail.com.